Will this work with multiple inserts?

[ironyx]

I have this trigger, and got an error message saying that I couldn't use FOR EACH ROW because it means I have a mutating table. I found a solution that takes the place of the row-level trigger, but it was terribly foreign to me (I'm very new to PL/SQL if you couldn't tell) and so I tried to think of another way. I was wondering if I just take the max value of the primary key, if that would work. The issue I was curious about is if I have a massive insert, will it do the trigger for each insert, or only work for the last one? Is there a better way, and if so, could you explain it in Pseudo English along with psuedo code...

thanks for the help and patience!
Va


CREATE OR REPLACE TRIGGER "OLDERVB"."AIRSPEED_PLN_METRICS_CURSOR"
AFTER
INSERT ON "OLDERVB"."STG_AIRSPEED_PLN_METRICS_TW"
DECLARE
v_tw_plan_cd NUMBER(10);
v_hold_date date;
CURSOR c1 IS
SELECT
MAX(TW.TW_PLAN_CD) <-- Will this work if multiple inserts are being done at once?
from stg_airspeed_pln_metrics_tw tw;
--
BEGIN
OPEN c1;
Fetch c1 into v_tw_plan_cd;

FOR m_counter IN 1..12 LOOP
IF (M_Counter > 9) THEN
v_hold_date:=LAST_DAY(TO_DATE(M_counter||'2004','MMYYYY'));
ELSE
v_hold_date:=LAST_DAY(TO_DATE('0'||M_counter||'2005','MMYYYY'));
END IF;
INSERT INTO STG_AIRSPEED_PLN_METRICS_COMPL (
RECORD_NB,
TW_PLAN_CD,
MONTH_END_DT,
AS_OF_DT)
VALUES (
RECORD_NB_SEQ.nextval,
v_tw_plan_cd,
v_hold_date,
SYSDATE);
exit when c1%notfound;
END LOOP;
CLOSE c1;
END;

 

[sem]

Obviously row-level trigger fires for each record and statement-level - for the whole insert (not for the last record!, so neither ld nor :new may be used).


Regards, Dima

http://seminihin.narod.ru

 

[ironyx]

Oh yeah, obviously... and does that mean no? See previous statement about my being a newbie.

I guess I am not sure if the trigger I have will work for a large insert.

I have to do a create table as... from an existing table (the table being created is my trigger table). Would I be able to use this trigger, or do you think since it only takes the max value, it will only take the max value of that insert?

I was thinking that if I had to, I could loop it so it does a create table from the other table line by line. Does any of this seem feasible.

Thanks for your time and knowledge!
Va

 

[ironyx]

Never mind, i guess I didn't think that a create doesn't really have inserts right? So I'll create the table and then do a loop through each row.

Thanks,
Va