Example of Invoking a Trigger Procedure

The following example defines three tables and a trigger procedure that references one of these tables in its FOR clause:

CREATE TABLE tab1 (col1 INT,col2 INT);
CREATE TABLE tab2 (col1 INT);
CREATE TABLE temptab1
   (old_col1 INTt, new_col1 INT, old_col2 INT, new_col2 INT);

/* The following procedure is invoked from an INSERT trigger in this example. 
*/  
CREATE PROCEDURE proc1()
REFERENCING OLD AS o NEW AS n FOR tab1; 

IF (INSERTING) THEN  -- INSERTING Boolean operator
        LET n.col1 = n.col1 + 1;   -- You can modify new values.
        INSERT INTO temptab1 VALUES(0,n.col1,1,n.col2);
END IF

IF (UPDATING) THEN  -- UPDATING Boolean operator
       -- you can access relevant old and new values. 
       INSERT INTO temptab1 values(o.col1,n.col1,o.col2,n.col2);
END IF

if (SELECTING) THEN  -- SELECTING Boolean operator
        -- you can access relevant old values.
        INSERT INTO temptab1 VALUES(o.col1,0,o.col2,0);
END IF

if (DELETING) THEN   -- DELETING Boolean operator
        DELETE FROM temptab1 WHERE temptab1.col1 = o.col1;
END IF

END PROCEDURE;

This example illustrates that the triggered action can be a different DML operation from the triggering event. Although this procedure inserts a row when an Insert trigger calls it, and deletes a row when a Delete trigger calls it, it also performs INSERT operations if it is called by a Select trigger or by an Update trigger.

The following statement defines an Insert trigger on tab1 that calls proc1( ) from the FOR EACH ROW section as its triggered action, and perform an INSERT operation that activates this trigger:

CREATE TRIGGER ins_trig_tab1 INSERT ON tab1 REFERENCING NEW AS post
  FOR EACH ROW(EXECUTE PROCEDURE proc1() WITH TRIGGER REFERENCES);

Note that the REFERENCING clause of the trigger declares a correlation name for the NEW value that is different from the correlation name that the trigger procedure declared. These names do not need to match, because the correlation name that was declared in the trigger procedure has that procedure as its scope of reference. The following statement activates the ins_trig_tab1 trigger, which executes the proc1( ) procedure.

INSERT INTO tab1 VALUES (111,222);

Because the trigger procedure increments the new value of col1 by 1, the value inserted is (112, 222), rather than the value that the triggering event specified.