--트리거 생성 코드 CREATE OR REPLACE TRIGGER emp_dw_triger after insert or update or delete ON emp for each row BEGIN
if inserting then insert into emp_dw (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (:NEW.EMPNO, :NEW.ENAME, :NEW.JOB, :NEW.MGR, :NEW.HIREDATE, :NEW.SAL, :NEW.COMM, :NEW.DEPTNO); elsif updating then update emp_dw set EMPNO = :NEW.EMPNO, ENAME = :NEW.ENAME, JOB = :NEW.JOB, MGR = :NEW.MGR, HIREDATE = :NEW.HIREDATE, SAL = :NEW.SAL, COMM = :NEW.COMM, DEPTNO = :NEW.DEPTNO where EMPNO = :OLD.EMPNO AND ENAME = :OLD.ENAME AND JOB = :OLD.JOB AND MGR = :OLD.MGR AND HIREDATE = :OLD.HIREDATE AND SAL = :OLD.SAL AND COMM = :OLD.COMM AND DEPTNO = :OLD.DEPTNO; elsif deleting then delete from emp_dw where EMPNO = :OLD.EMPNO AND ENAME = :OLD.ENAME AND JOB = :OLD.JOB AND MGR = :OLD.MGR AND HIREDATE = :OLD.HIREDATE AND SAL = :OLD.SAL AND COMM = :OLD.COMM AND DEPTNO = :OLD.DEPTNO; end if; END; /
--트리거 테스트 코드 INSERT INTO emp SELECT * FROM emp_dw; update emp set job = '외계인' where deptno = 10; delete from emp where job = 'MANAGER';
--테스트 결과 확인 코드 select * from emp; select * from emp_dw;
--테이블 원상복구 코드 @demo; delete from emp_dw; insert into emp_dw select * from emp; |
첫댓글 or 가 아니라 and 로 변경해줘서 테스트 해봐야합니다. 또는 where 절에 empno 만 둬야합니다.