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.

NEW Can be Skipped in DB2, but Required in Oracle

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:

name created
John 2013-07-12 12.17.19.195000

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.

More Information

About SQLines

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.

You could leave a comment if you were logged in.