SQL Optimizer for DB2 LUW 4.4 - Installation Guide

Trigger Conversion

There are two temporary tables which identifies the set of affected rows prior to the triggering operation ( OLD_TABLE ) and which identifies the set of affected rows after the triggering operation ( NEW_TABLE ) defined the triggers REFERENCING clause. These temporary tables cannot be referenced outside the trigger body; therefore to simulate the SQL statement execution, creation of two temp tables are used to simulate the OLD_TABLE and NEW_TABLE temporary tables.

Trigger

CREATE TRIGGER TRG_DEL_TEST1

 AFTER DELETE on department

REFERENCING OLD_TABLE AS dept_old

FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    INSERT INTO dept_deleted (cola, dpt_id, dpt_name)

    SELECT 1,

            dpt_id,

            dpt_name

      FROM dept_old;

END

Scanned SQL

INSERT INTO dept_deleted (cola, dpt_id, dpt_name)

SELECT 1,

       dpt_id,

       dpt_name

  FROM dept_old;

After conversion

DECLARE GLOBAL TEMPORARY TABLE dept_old

LIKE department

NOT LOGGED

 

INSERT INTO dept_deleted (cola, dpt_id, dpt_name)

SELECT 1,

       dpt_id,

       dpt_name

  FROM dept_old;

Related Topics

SQL Scanner Conversions Overview