NEW Correlation Name in Triggers - IBM DB2 to Oracle Migration

In a FOR EACH ROW trigger, you can use NEW correlation name in DB2 and :NEW in Oracle to refer to the new values of columns.

DB2	Oracle
NEW.column	:NEW.column in the trigger body	NEW.column in WHEN condition
NEW can be omitted in assignment	:NEW must be specified in assignment

Note: DB2 and Oracle allow you to change alias NEW to any other identifier using REFERENCING NEW AS name clause.

In DB2, you can assign a value to a column without specifying NEW if there is REFERENCING NEW AS clause defined in the trigger.

DB2:

  -- Sample table
  CREATE TABLE employees
  (
      name VARCHAR(70),
      created TIMESTAMP
  );
 
  -- CREATED column is referenced without NEW.
  CREATE TRIGGER employees_tr
    BEFORE INSERT ON employees
    REFERENCING NEW AS NEW
    FOR EACH ROW MODE DB2SQL
      SET created = CURRENT TIMESTAMP;
  # DB20000I  The SQL command completed successfully.
 
  -- Insert data
  INSERT INTO employees (name) VALUES ('John');
 
  -- Query data
  SELECT * FROM employees;

The SELECT statement returns:

In Oracle, you must specify the :NEW correlation name in the assignment statements:

Oracle:

  -- Sample table
  CREATE TABLE employees
  (
      name VARCHAR2(70),
      created TIMESTAMP
  );
 
  -- Trying to refer CREATED column without :NEW.
  CREATE OR REPLACE TRIGGER employees_tr
    BEFORE INSERT ON employees
    REFERENCING NEW AS NEW
    FOR EACH ROW
   BEGIN
      created := SYSTIMESTAMP;
   END;
   /
  # Warning: Trigger created with compilation errors.
  # PLS-00201: identifier 'CREATED' must be declared
 
 -- Now use :NEW.CREATED 
  CREATE OR REPLACE TRIGGER employees_tr
    BEFORE INSERT ON employees
    REFERENCING NEW AS NEW
    FOR EACH ROW
   BEGIN
      :new.created := SYSTIMESTAMP;
   END;
   /
   # Trigger created.

• IBM DB2 to Oracle Migration Tools and Reference

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko - July 2013.