Defining Multiple Update Triggers

Multiple Update triggers on a table cannot include the same columns. In the following example, trig3 is not valid on the items table because its column list includes stock_num, which is a triggering column in trig1.

CREATE TRIGGER trig1 UPDATE OF item_num, stock_num ON items
   REFERENCING OLD AS pre NEW AS post
   FOR EACH ROW(EXECUTE PROCEDURE proc1());
CREATE TRIGGER trig2 UPDATE OF manu_code ON items
   BEFORE(EXECUTE PROCEDURE proc2());
-- Invalid trigger: stock_num occurs in trig1
CREATE TRIGGER trig3 UPDATE OF order_num, stock_num ON items
   BEFORE(EXECUTE PROCEDURE proc3());

When an UPDATE statement updates multiple columns that have different triggers, the column numbers of the triggering columns determine the order of trigger execution. Execution begins with the smallest triggering column number and proceeds in order to the largest triggering column number. The following example shows that table taba has four columns (a, b, c, d):

CREATE TABLE taba (a int, b int, c int, d int)

Define trig1 as an update on columns a and c, and define trig2 as an update on columns b and d, as the following example shows:

CREATE TRIGGER trig1 UPDATE OF a, c ON taba
   AFTER (UPDATE tabb SET y = y + 1);

CREATE TRIGGER trig2 UPDATE OF b, d ON taba
   AFTER (UPDATE tabb SET z = z + 1);

The following example shows a triggering statement for the Update trigger:

UPDATE taba SET (b, c) = (b + 1, c + 1)

Then trig1 for columns a and c executes first, and trig2 for columns b and d executes next. In this case, the smallest column number in the two triggers is column 1 (a), and the next is column 2 (b).