트리거 PL/SQL

[21기_남준혁]

--트리거 생성 코드
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 만 둬야합니다.