Examples of copying triggers

These examples show the DDL that is generated when you copy triggers between heterogeneous databases. The triggers are copied as part of the tables to which they belong.

The generated DDL is shown on the Preview DDL page in the Paste Database Objects wizard.

Table 1 shows examples of the DDL that is generated for copying triggers and the tables to which they belong from an Oracle Database 11g source database to a DB2® Version 9.7 for Linux, UNIX, and Windows target database.

Table 1. Examples of copying triggers and their tables from Oracle Database 11g to DB2 Version 9.7 for Linux, UNIX, and Windows database
Oracle Database 11g table and trigger definition Generated DDL for DB2 Version 9.7 database 
CREATE TABLE t4
  (a NUMBER,
   b VARCHAR2(20)
  );

CREATE TRIGGER trig1
   AFTER INSERT ON t4
   REFERENCING NEW AS NEW
   FOR EACH ROW
   WHEN (new.a < 10)
   BEGIN
     INSERT INTO t5 values(:new.b, :new.a);
   END trig1;
 
CREATE TABLE T4
  (A NUMBER,
   B VARCHAR2(20)
  );

CREATE TRIGGER TRIG1
   AFTER INSERT ON T4
   REFERENCING NEW AS NEW
   FOR EACH ROW
   WHEN (NEW.A < 10)
   BEGIN
     INSERT INTO T5 VALUES (:NEW.B, :NEW.A);
   END TRIG1;
 
CREATE TABLE DEPT
  (DEPTNO NUMBER(2 , 0) NOT NULL,
   DNAME VARCHAR2(14),
   LOC VARCHAR2(13),
   MGR_NO NUMBER,
   DEPT_TYPE NUMBER
  );

CREATE TABLE EMP
  (EMPNO NUMBER NOT NULL,
   ENAME VARCHAR2(10),
   JOB VARCHAR2(9),
   MGR NUMBER(4 , 0),
   HIREDATE DATE,
   SAL NUMBER(7 , 2),
   COMM NUMBER(7 , 2),
   DEPTNO NUMBER(2 , 0) NOT NULL
  );

CREATE TABLE PROJECT_TAB
  (PRJ_LEVEL NUMBER,
   PROJNO NUMBER,
   RESP_DEPT NUMBER
  );

CREATE TRIGGER LOG_SALARY_INCREASE
   AFTER UPDATE OF MGR, EMPNO, ENAME, SAL,
      HIREDATE, DEPTNO, JOB, COMM ON EMP
   REFERENCING NEW AS NEW
   OLD AS OLD
   FOR EACH ROW
   WHEN (NEW.Sal > 1000)
   BEGIN
     INSERT INTO Emp_log
       (Emp_id, Log_date, New_salary, Action)
     VALUES
       (:NEW.Empno, SYSDATE, :NEW.SAL, 'NEW SAL');
   END;

CREATE TRIGGER LOG_EMP_UPDATE
   AFTER UPDATE OF EMPNO, HIREDATE, MGR, SAL,
      ENAME, JOB, COMM, DEPTNO ON EMP
   REFERENCING NEW AS NEW
   OLD AS OLD
   FOR EACH STATEMENT
   BEGIN
     INSERT INTO Emp_log
       (Log_date, Action)
     VALUES
       (SYSDATE, 'emp COMMISSIONS CHANGED');
   END;
 
CREATE TABLE DEPT
  (DEPTNO SMALLINT NOT NULL,
   DNAME VARCHAR(14),
   LOC VARCHAR(13),
   MGR_NO DECFLOAT(16),
   DEPT_TYPE DECFLOAT(16)
  );

CREATE TABLE EMP
  (EMPNO DECFLOAT(16) NOT NULL,
   ENAME VARCHAR(10),
   JOB VARCHAR(9),
   MGR SMALLINT,
   HIREDATE TIMESTAMP,
   SAL DECIMAL(7 , 2),
   COMM DECIMAL(7 , 2),
   DEPTNO SMALLINT NOT NULL
  );

CREATE TABLE PROJECT_TAB
  (PRJ_LEVEL DECFLOAT(16),
   PROJNO DECFLOAT(16),
   RESP_DEPT DECFLOAT(16)
  );

CREATE TRIGGER LOG_SALARY_INCREASE
   AFTER UPDATE ON EMP

   REFERENCING NEW AS NEW
   OLD AS OLD
   FOR EACH ROW
   WHEN (NEW.Sal > 1000)
   BEGIN
     INSERT INTO Emp_log
       (Emp_id, Log_date, New_salary, Action)
     VALUES
       (:NEW.Empno, SYSDATE, :NEW.SAL, 'NEW SAL');
   END;

CREATE TRIGGER LOG_EMP_UPDATE
   AFTER UPDATE OF EMPNO, HIREDATE, MGR, SAL,
      ENAME, JOB, COMM, DEPTNO ON EMP
   REFERENCING NEW AS NEW
   OLD AS OLD
   FOR EACH STATEMENT
   BEGIN
     INSERT INTO Emp_log
       (Log_date, Action)
     VALUES
       (SYSDATE, 'emp COMMISSIONS CHANGED');
   END;
 
CREATE TABLE letter
  (x INT NOT NULL PRIMARY KEY,
   y INT
   );
 
CREATE TABLE columns
  (col1 INT REFERENCES letter,
   col2 INT CHECK
     (col2 > 0)
  ); 

CREATE INDEX columns_idx
   ON columns(col2, col1);

CREATE TRIGGER trig1
   BEFORE INSERT OR UPDATE
   OF col1, col2 ON columns
   FOR EACH ROW
   BEGIN
      IF
        ( :new.col1 < :new.col2 )
      THEN
        raise_application_error(-20001,
        'Invalid operation col1 cannot be
        less then col2');
      END IF;
   END;
 
CREATE TABLE LETTER
  (X DECFLOAT(34) NOT NULL PRIMARY KEY,
   Y DECFLOAT(34)
   );
 
CREATE TABLE COLUMNS
  (COL1 DECFLOAT(34) REFERENCES LETTER,
   COL2 DECFLOAT(34) CHECK
     (COL2 > 0)
  ); 

CREATE INDEX COLUMNS_IDX
   ON COLUMNS(COL2, COL1);

CREATE TRIGGER TRIG1
   BEFORE INSERT
   OF COL1, COL2 ON COLUMNS
   FOR EACH ROW
   BEGIN IF
     ( :NEW.COL1 < :NEW.COL2 )
     THEN
       RAISE_APPLICATION_ERROR(-20001,
       'Invalid operation col1 cannot be
        less then col2');
      END IF;
   END;

Table 2 shows an example of the DDL that is generated for copying a trigger from a DB2 Version 9.7 for Linux, UNIX, and Windows source database to an Oracle Database 11g target database.

Table 2. Example of copying a trigger from DB2 Version 9.7 for Linux, UNIX, and Windows database to Oracle Database 11g
DB2 Version 9.7 database trigger definition Generated DDL for Oracle Database 11g 
CREATE OR REPLACE TRIGGER emp_comm_trig
   BEFORE INSERT ON emp
   FOR EACH ROW
   BEGIN
     IF :NEW.deptno = 30 THEN
       :NEW.comm := :NEW.sal * .4;
     END IF;
   END;
 
CREATE OR REPLACE TRIGGER EMP_COMM_TRIG
   BEFORE INSERT ON EMP
   FOR EACH ROW
   BEGIN
     IF :NEW.DEPTNO = 30 THEN
       :NEW.COMM := :NEW.SAL * .4;
     END IF;
   END;

Table 3 shows examples of the DDL that is generated for copying a trigger from a DB2 Version 9.7 for Linux, UNIX, and Windows source database to a DB2 Version 9.1 for z/OS® database.

Table 3. Examples of copying a trigger from a DB2 Version 9.7 for Linux, UNIX, and Windows database to DB2 Version 9.1 for z/OS database
DB2 Version 9.7 for Linux, UNIX, and Windows database trigger definition Generated DDL for DB2 Version 9.1 for z/OS 
CREATE TABLE test21
  (col1 CHAR(20),
   col2 VARCHAR(10),
   col3 INTEGER,
   col4 DECIMAL(10,9));

CREATE TABLE test21a
  (col1 CHAR(20),
   col2 VARCHAR(10),
   col3 INTEGER,
   col4 DECIMAL(10,9));

CREATE TRIGGER t_test211
   AFTER INSERT ON test21
   FOR EACH ROW MODE DB2SQL
   BEGIN ATOMIC
      UPDATE test21a SET col3 = col3 + 1;
   END;   
CREATE TABLE TEST21
  (COL1 CHAR(20),
   COL2 VARCHAR(10),
   COL3 INTEGER,
   COL4 DECIMAL(10,9));

CREATE TABLE TEST21A
  (COL1 CHAR(20),
   COL2 VARCHAR(10),
   COL3 INTEGER,
   COL4 DECIMAL(10,9));

CREATE TRIGGER NEWTON.T_TEST211
   AFTER INSERT ON NEWTON.TEST21
   FOR EACH ROW MODE DB2SQL
   BEGIN ATOMIC
      UPDATE TEST21A SET COL3 = COL3 + 1;
   END;
 
CREATE TRIGGER t_test21a
   AFTER UPDATE OF
      col1, col2, col3, col4
   ON test21
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW MODE DB2SQL
   WHEN (NEW.col3 < 1000)
   BEGIN ATOMIC
      INSERT INTO test21a
         (col1, col2, col3, col4) 
      VALUES ('HI', 'NEW SAL', 10, null); 
   END;
 
CREATE TRIGGER NEWTON.T_TEST21A
   AFTER UPDATE ON NEWTON.TEST21
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW MODE DB2SQL
   WHEN (NEW.COL3 < 1000)
   BEGIN ATOMIC
      INSERT INTO TEST21A
         (COL1, COL2, COL3, COL4)
      VALUES ('HI', 'NEW SAL', 10, null); 
   END;
 
CREATE TRIGGER t_test21e
   AFTER INSERT ON test21
   FOR EACH ROW
   UPDATE test21a SET col3 = col3 + 1;
 
CREATE TRIGGER NEWTON.T_TEST21E
   AFTER INSERT ON NEWTON.TEST21
   FOR EACH ROW MODE DB2SQL
   UPDATE TEST21A SET COL3 = COL3 + 1;

Table 4 shows examples of the DDL that is generated for copying a trigger from a DB2 Version 9.1 for z/OS source database to a DB2 Version 9.7 for Linux, UNIX, and Windows database.

Table 4. Examples of copying a trigger from a DB2 Version 9.1 for z/OS database to DB2 Version 9.7 for Linux, UNIX, and Windows database
DB2 Version 9.1 for z/OS database trigger definition Generated DDL for DB2 Version 9.7 for Linux, UNIX, and Windows 
CREATE TBLE test21z
  (col1 CHAR(20),
   col2 VARCHAR(10),
   col3 INTEGER,
   col4 DECIMAL(10,9));

create table test21az
  (col1 CHAR(20),
   col2 VARCHAR(10),
   col3 INTEGER,
   col4 DECIMAL(10,9));

CREATE TRIGGER t_test21cz
   AFTER UPDATE OF
      col1, col2, col3, col4
   ON test21z
   FOR EACH STATEMENT MODE DB2SQL
   BEGIN ATOMIC
      INSERT INTO test21az
         (col1, col2, col3, col4)
      VALUES ('HI', 'NEW SAL', 10, null); 
   END;
 
CREATE TABLE TEST21Z
  (COL1 CHAR(20),
   COL2 VARCHAR(10),
   COL3 INTEGER,
   COL4 DECIMAL(10,9));

CREATE TABLE TEST21AZ
  (COL1 CHAR(20),
   COL2 VARCHAR(10),
   COL3 INTEGER,
   COL4 DECIMAL(10,9));

CREATE TRIGGER DB2ADMIN.T_TEST21CZ
   AFTER UPDATE ON DB2ADMIN.TEST21Z
   FOR EACH STATEMENT MODE DB2SQL
   BEGIN ATOMIC
      INSERT INTO TEST21AZ
         (COL1, COL2, COL3, COL4)
      VALUES ('HI', 'NEW SAL', 10, null);
   END;
 
CREATE TRIGGER t_test21dz
   NO CASCADE BEFORE UPDATE ON test21z
   REFERENCING NEW AS NEW
   FOR EACH ROW MODE DB2SQL
   WHEN (NEW.col3 < 1000)
   BEGIN ATOMIC
      SET NEW.col3 = NEW.col3 + 1;
   END;
 
CREATE TRIGGER DB2ADMIN.T_TEST21DZ
   NO CASCADE BEFORE UPDATE ON DB2ADMIN.TEST21Z
   REFERENCING NEW AS NEW
   FOR EACH ROW MODE DB2SQL
   WHEN (NEW.COL3 < 1000)
   BEGIN ATOMIC
      SET NEW.COL3 = NEW.COL3 + 1;
   END;
Related information:
Examples of copying database objects
Feedback