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;
|