ORACLE and TRIGGERS inserted updated deleted

Question

I would like to use a trigger on a table which will be fired every time a row is inserted  updated  or deleted   I wrote something like this   CREATE or REPLACE TRIGGER test001   AFTER INSERT OR DELETE OR UPDATE ON tabletest001   REFERENCING OLD AS old buffer NEW AS new buffer    FOR EACH ROW WHEN  new buffer field1    HBP00      and it works   Since I would like to do the same things if the row is inserted  updated  or deleted  I would like to know what s happening in the trigger  I think I can manage to find if the row is inserted or updated  I can check the old buffer with the new buffer   How can I know if the row has been deleted

User · Answer

The NEW values  or NEW BUFFER as you have renamed them  are only available when INSERTING and UPDATING   For DELETING you would need to use OLD  OLD BUFFER    So your trigger would become   CREATE or REPLACE TRIGGER test001   AFTER INSERT OR DELETE OR UPDATE ON tabletest001   REFERENCING OLD AS old buffer NEW AS new buffer    FOR EACH ROW WHEN  new buffer field1    HBP00  OR old buffer field1    HBP00      You may need to add logic inside the trigger to cater for code that updates field1 from  HBP000  to something else

User · Answer

Separate it into 2 triggers  One for the deletion and one for the insertion  update

User · Answer

From Using Triggers      Detecting the DML Operation That Fired   a Trigger      If more than one type of DML operation   can fire a trigger  for example  ON   INSERT OR DELETE OR UPDATE OF   Emp tab   the trigger body can use the   conditional predicates INSERTING    DELETING  and UPDATING to check which   type of statement fire the trigger    So  IF DELETING THEN     END IF    should work for your case

User · Answer

I ve changed my code like this and it works   CREATE or REPLACE TRIGGER test001   AFTER INSERT OR UPDATE OR DELETE ON tabletest001   REFERENCING OLD AS old buffer NEW AS new buffer    FOR EACH ROW WHEN  new buffer field1    HBP00  OR old buffer field1    HBP00     DECLARE       Operation       NUMBER        CustomerCode    CHAR 10 BYTE   BEGIN  IF DELETING THEN    Operation    3    CustomerCode     old buffer field1  END IF   IF INSERTING THEN    Operation    1    CustomerCode     new buffer field1  END IF   IF UPDATING THEN    Operation    2    CustomerCode     new buffer field1  END IF          DO SOMETHING      EXCEPTION     WHEN OTHERS THEN ErrorCode    SQLCODE   END

[oracle] ORACLE and TRIGGERS (inserted, updated, deleted)

Examples related to oracle

Examples related to triggers