La DDL générée est présentée sur la page Aperçu du fichier DDL dans l'assistant Coller des objets de base de données.
Le tableau 1 présente des exemples de la DDL générée pour la copie de déclencheurs d'une base de données source Oracle 11g vers une base de données cible DB2 Version 9.7 for Linux, UNIX, and Windows.
Définition de la table et du déclencheur de la base de données Oracle 11g | DDL générée pour la base de données 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; |
Le tableau 2 présente un exemple de la DDL générée pour la copie d'un déclencheur d'une base de données source DB2 Version 9.7 for Linux, UNIX, and Windows vers une base de données cible Oracle 11g.
Définition du déclencheur de la base de données DB2 Version 9.7 | DDL générée pour la base de données Oracle 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; |
Le tableau 3 présente des exemples de la DDL générée pour la copie d'un déclencheur d'une base de données source DB2 Version 9.7 for Linux, UNIX, and Windows vers une base de données DB2 Version 9.1 for z/OS.
Définition du déclencheur de la base de données DB2 Version 9.7 for Linux, UNIX, and Windows | DDL générée pour la base de données 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; |
Le tableau 4 présente des exemples de la DDL générée pour la copie d'un déclencheur d'une base de données source DB2 Version 9.1 for z/OS vers une base de données DB2 Version 9.7 for Linux, UNIX, and Windows.
Définition du déclencheur de la base de données DB2 Version 9.1 for z/OS | DDL générée pour la base de données 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; |