15일차 # 10-14: DML Row Trigger

DML Row Trigger <-- FOR EACH ROW가 있음

create or replace trigger emp_trg_2

before insert or update of sal on t_emp

for each row

begin

 if not (:new.job in ('PRESIDENT', 'ANALYST'))

 and :new.sal >= 3000 then

   raise_application_error(-20002, '급여를 3000 달러 이상 받을 수 없는 직군입니다.');

          end if;

end;

/

insert into t_emp (empno, ename, job, sal)

 values (1112, 'THOMPSON', 'ANALYST', 3500);

insert into t_emp (empno, ename, job, sal)

 values (1113, 'GIBSON', 'SALESMAN', 2200);

insert into t_emp (empno, ename, job, sal)

 values (1114, 'KLEIN', 'CLERK', 3000);

CREATE OR REPLACE TRIGGER restrict_salary

 BEFORE INSERT OR UPDATE OF salary ON employees

 FOR EACH ROW --> 행 트리거입니다

BEGIN

          dbms_output.put_line('retrict salary trigger is performed!');

 IF NOT (:NEW.job_id IN ('AD_PRES', 'AD_VP'))

    AND :NEW.salary > 15000 THEN

   RAISE_APPLICATION_ERROR (-20202, 'Employee cannot earn more than $15,000.');

 END IF;

END;

/

drop trigger secure_emp;

set serveroutput on

insert into employees (employee_id, last_name, email, hire_date, job_id, salary)

values (3333, 'Lee', 'lee@...', sysdate-1, 'AD_VP', 20000);

insert into employees (employee_id, last_name, email, hire_date, job_id)

values (4444, 'JANG', 'jang@...', sysdate-1, 'AD_ASST');

insert into employees (employee_id, last_name, email, hire_date, job_id, salary)

values (5555, 'JUNG', 'jung@...', sysdate-1, 'AD_ASST', 16000);

update employees

set last_name = 'CHO'

where employee_id = 4444;

update employees

set salary = 10000

where employee_id = 4444;

update employees

set salary = 16000

where employee_id = 4444; 에러: --> ORA-20202: Employee cannot earn more than $15,000.