Die generierte DDL-Datei wird auf der Seite DDL voranzeigen im Assistenten Datenbankobjekte einfügen angezeigt.
In Tabelle 1 werden Beispiele für die DDL gezeigt, die für das Kopieren von Triggern und der Tabellen, zu denen sie gehören, aus einer Oracle Database 11g-Quellendatenbank in eine DB2 Version 9.7 für Linux®, UNIX® und Windows®-Zieldatenbank generiert wird.
Oracle Database 11g - Tabellen- und Triggerdefinition | Generierte DDL für Datenbank in DB2 Version 9.7 |
---|---|
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; |
In Tabelle 2 wird ein Beispiel für die DDL gezeigt, die für das Kopieren eines Triggers aus einer DB2 Version 9.7 für Linux, UNIX und Windows-Quellendatenbank in eine Oracle Database 11g-Zieldatenbank generiert wird.
DB2 Version 9.7 - Datenbanktriggerdefinition | Generierte DDL für 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; |
In Tabelle 3 werden Beispiele für die DDL gezeigt, die für das Kopieren eines Triggers aus einer DB2 Version 9.7 für Linux, UNIX und Windows-Quellendatenbank in eine DB2 Version 9.1 für z/OS-Datenbank generiert wird.
DB2 Version 9.7 für Linux, UNIX, and Windows - Datenbanktriggerdefinition | Generierte DDL für DB2 Version 9.1 für 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; |
In Tabelle 4 werden Beispiele für die DDL gezeigt, die für das Kopieren eines Triggers von einer DB2 Version 9.1 für z/OS-Quellendatenbank in eine DB2 Version 9.7 für Linux, UNIX und Windows-Datenbank generiert wird.
DB2 Version 9.1 für z/OS - Datenbanktriggerdefinition | Generierte DDL für DB2 Version 9.7 für Linux, UNIX und 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; |