In DB2, is it possible to have only one trigger for updating and inserting?

Question

I need to create a trigger (s) that will store an audit of my table. It is assumed that the trigger will be executed for both insert and update.

I currently have two triggers

One to insert :

CREATE TRIGGER SCH.TRG_TBL1_AFT_I AFTER INSERT ON SCH.TBL1 REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL INSERT INTO SCH.TBL1_AUDIT VALUES( .. ,, .. ); 

Another to update

 CREATE TRIGGER SCH.TRG_TBL1_AFT_U AFTER UPDATE ON SCH.TBL1 REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL INSERT INTO SCH.TBL1_AUDIT VALUES( .. ,, .. ); 

But the fact is, is it possible to create a single trigger in DB2 to complete a task? [provided that both triggers do the same thing.]

Answer 1

Sorry, DB2 does not offer a way to combine updates and insert triggers together.

Answer 2

try it

 CREATE or replace TRIGGER PASSENGER_TR01_BEFORE_IUD BEFORE DELETE OR UPDATE OF FIRST_NAME OR INSERT ON PASSENGER REFERENCING OLD AS oldRow NEW AS newRow FOR EACH ROW WHEN (1=1) Begin Declare ACTION Char(1) Default ''; -- Use Case/When to inquire trigger-event Case When INSERTING Then Set ACTION='I'; When UPDATING Then Set ACTION='U'; When DELETING Then Set ACTION='D'; Else Set ACTION='N'; End Case; -- Use If/Then/Else to inquire trigger-event If INSERTING Then Set ACTION='I'; ElseIf UPDATING Then Set ACTION='U'; ElseIf DELETING Then Set ACTION='D'; Else Set ACTION='N'; End If; End 

Answer 3

Yes it is possible. See the documentation for creating a trigger . Here is the pasta:

 trigger-event .-OR--------------------------------------. V (4) | |----+-INSERT--------------------------+-----+------------------| +-DELETE--------------------------+ '-UPDATE--+---------------------+-' | .-,-----------. | | V | | '-OF----column-name-+-' 

This will let you say:

create trigger blah before insert on blah or update of blah .

Answer 4

This feature is now in DB2 10.x. It was available sine 9.7.x

Answer 5

You can find more information here.

  CREATE OR REPLACE TRIGGER SET_SALARY NO CASCADE BEFORE UPDATE OR INSERT ON employee REFERENCING NEW AS n OLD AS o FOR EACH ROW WHEN( o.edlevel IS NULL OR n.edlevel > o.edlevel ) BEGIN -- Give 10% raise to existing employees with new education level. IF UPDATING THEN SET n.salary = n.salary * 1.1; -- Give starting salary based on education level. ELSEIF INSERTING THEN SET n.salary = CASE n.edlevel WHEN 18 THEN 50000 WHEN 16 THEN 40000 ELSE 25000 END; END IF; END