083 - [Oracle PL/SQL] DML Triggers - row-level sample

new, old 정의할것.

 

 

 

• 오류 발생 코드가 있어서 1회만 실행되는 샘플 코드

DROP TABLE EMP_COPY;
/
CREATE TABLE EMP_COPY
AS SELECT * FROM employees;
/
SELECT EMPLOYEE_ID, FIRST_NAME,LAST_NAME, SALARY FROM EMP_COPY;


# 옵션은 row level trigger 이지만, 오류를 발생시키므로 1회만 동작된다.
create or replace trigger check_sal
before
insert or update of salary
on
EMP_COPY
for each row -- row trigger 옵션
begin
  if :new.salary<500 then
  raise_application_error(-20030, 'min sal is 500');
  end if;
end;
-------------------



update EMP_COPY
set salary=200
where employee_id=100;--해당 조건이 1건이므로 트리거가 1번 실행
-------------------------------
Error report -
SQL Error: ORA-20030: min sal is 500
ORA-06512: at "HR.CHECK_SAL", line 3
ORA-04088: error during execution of trigger 'HR.CHECK_SAL'



UPDATE EMP_COPY
set salary=200;
--조건에 맞는 개수만큼 트리거가 실행되야 하지만, 
--raise_application_error 옵션으로 오류를 강제로 발생시킴, sql 강제 종료.
-------------------------------
Error report -
SQL Error: ORA-20030: min sal is 500
ORA-06512: at "HR.CHECK_SAL", line 3
ORA-04088: error during execution of trigger 'HR.CHECK_SAL'


# 조건문 때문에 업데이트된 결과는 없다
SELECT EMPLOYEE_ID, FIRST_NAME,LAST_NAME, SALARY FROM EMP_COPY
where salary=200;
---------------------
no rows selected



SELECT COUNT(1) FROM EMP_COPY;

select * from user_triggers
where trigger_name='CHECK_SAL';

 

 

 

• (오류 발생 코드가 없어서) 여러번 트리거가 발생하는 샘플 코드

create or replace trigger check_sal
before
insert or update of salary
on
EMP_COPY
REFERENCING NEW AS NEW OLD AS OLD --레퍼런스 재정의, 이름이 같아서 의미없음.
for each row
begin
    if :new.salary<500 then
        -- 오류를 발생시키지 않으므로, row 트리거로 동작.
        dbms_output.put_line('s');
    end if;
end;



update EMP_COPY
set salary=200
where employee_id=100;
----------------------------
s << 1번 출력됨.

1 row updated.


-- s 107번 출력.
update EMP_COPY
set salary=200;
----------------------------
s
s
s
...
s
s
s


107 rows updated.

 

 

 

• 레퍼런스명을 재정의 하는 샘플 코드

# 레퍼런스명 재정의

create or replace trigger check_sal
before
insert or update of salary
on
EMP_COPY
REFERENCING NEW AS N OLD AS O --레퍼런스명 재정의
for each row
begin
  if :n.salary<500 then
    dbms_output.put_line('s');
end if;
end;
--------


# 실행결과는 동일하여 생략
update EMP_COPY
set salary=200
where employee_id=100;

update EMP_COPY
set salary=200;