This table will have the same columns as the sales table plus a column for the change date and a column for the change method. create or replace trigger sales_history_tr 2 before update or delete on sales 3 for each row 4 begin 5 if (updating) then 6 insert into sales_history 7 ( CHG_DATE 8 , CHG_TYPE 9 , STORE_KEY 10 , BOOK_KEY 11 , ORDER_NUMBER 12 , ORDER_DATE 13 , QUANTITY) 14 values 15 ( sysdate 16 , 'UPDATE' 17 , :old. Using this capability, the trigger can execute different sections of code based on the even that executed. QUANTITY); 22 else 23 insert into sales_history 24 ( CHG_DATE 25 , CHG_TYPE 26 , STORE_KEY 27 , BOOK_KEY 28 , ORDER_NUMBER 29 , ORDER_DATE 30 , QUANTITY) 31 values 32 ( sysdate 33 , 'DELETE' 34 , :old. You can redefine these names using the referencing clause. The thr To see this capability in action, first create the sales_history table to hold the old rows from the sales table. The example below replaces the standard notation with early and late create or replace trigger sales_history2_tr 2 before update or delete on sales 3 referencing old as early new as late 4 for each row 5 begin 6 insert into sales_history 7 ( CHG_DATE, 8 CHG_TYPE, 9 STORE_KEY, 10 BOOK_KEY, 11 ORDER_NUMBER, 12 ORDER_DATE, 13 QUANTITY) 14 values 15 ( sysdate, 16 'UPDATE', 17 :early. Here in the above Oracle PLSQL trigger syntax the ‘trigger_name’ is the name given to AFTER INSERT TRIGGER.We cannot create an AFTER INSERT TRIGGER on an Oracle PLSQL VIEW.
All three databases support row triggers before and after insert, update and delete statements.
4) instead of this " UPDATE GL_ORDER SET TRANS_KEY=tmp Var; " try something like :new.trans_key := tmp Var; 5) no need of commit.
Hi, I had earlier tried the way you explained but I was getting mutatiion error regardless.
Can you help me with a trigger for each row that is the same as Oracle?
Triggers in SQL Server do not actually fire only for the last row affected; Rather, they fire once for the entire set of affected rows.