Defining Multiple Update Triggers

Multiple Update triggers on a table can include the same or different columns. In the following example, trig3 on the items table includes in its column list 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());
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 firing order is based on the lowest-numbered column that is defined in the triggers that actually fired, regardless of whether that lowest-numbered column was actually the triggering column when the trigger fired. If several Update triggers are set on the same column or on the same set of columns, however, the order of trigger execution is not guaranteed.

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).