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?

Solution 1

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.

Solution 2

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;

Solution 3

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.

Solution 4

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

View more solutions

Comments

[LeftyX]

Thanks for your answer. It works. I have some problems with DELETING. I guess it is not trapped cause of this condition new_buffer.field1 = 'HBP00'.

[LeftyX]

Thanks but I wanted to know how to identify the operation. Alberto

[LeftyX]

I've figured it out 10 minutes ago ;-) Thanks. Alberto

[pauloya]

@LeftyX That's why you should accept devio's answer, not Tony Andrews.

[GACy20]

How can you pass these information to other (e.g.) functions? For example, say I want to compute a value depending on whether it is inserting/updating/deleting and I have to do this in dozens of triggers. I'd like to make the code as DRY as possible, so ideally I'd like to be able to do value := my_func(INSERTING, UPDATING, DELETING, ...other params) and just copy&paste that line in all my triggers... but what type should I give to the 3 parameters of my_func? Would this even work? Or we must use if inserting then my_insert_func(...other params) elsif updating then my_update_func(...) ?