생성된 DDL은 데이터베이스 오브젝트 붙여넣기 마법사의 DDL 미리보기 페이지에 표시됩니다.
표 1은 Oracle Database 11g 소스 데이터베이스에서 Linux®, UNIX® 및 Windows®용 DB2® 버전 9.7 목표 데이터베이스로 트리거 및 트리거가 속한 테이블을 복사하는 경우 생성되는 DDL의 예를 보여줍니다.
| Oracle Database 11g 테이블 및 트리거 정의 | DB2 버전 9.7 데이터베이스에 대해 생성된 DDL |
|---|---|
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;
|
표 2는 Linux, UNIX 및 Windows용 DB2 버전 9.7 소스 데이터베이스에서 Oracle Database 11g 목표 데이터베이스로 트리거를 복사하는 경우 생성되는 DDL의 예를 보여줍니다.
| DB2 버전 9.7 데이터베이스 트리거 정의 | Oracle Database 11g에 대해 생성된 DDL |
|---|---|
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;
|
표 3은 Linux, UNIX 및 Windows용 DB2 버전 9.7 소스 데이터베이스에서 z/OS®용 DB2 버전 9.1 데이터베이스로 트리거를 복사하는 경우 생성되는 DDL의 예를 보여줍니다.
| Linux, UNIX 및 Windows용 DB2 버전 9.7 데이터베이스 트리거 정의 | z/OS용 DB2 버전 9.1에 대해 생성된 DDL |
|---|---|
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; |
표 4는 z/OS용 DB2 버전 9.1 소스 데이터베이스에서 Linux, UNIX 및 Windows용 DB2 버전 9.7 데이터베이스로 트리거를 복사하는 경우 생성되는 DDL의 예를 보여줍니다.
| z/OS용 DB2 버전 9.1 데이터베이스 트리거 정의 | Linux, UNIX 및 Windows용 DB2 버전 9.7에 대해 생성된 DDL |
|---|---|
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;
|