085 - [Oracle PL/SQL] Trigger - Firing sequence

오라클에서 트리거가 실행되는 순서에 대해서 알아보자

 

 

• 오라클에서는 4 종류의 트리거가 순서대로 발생 - 5개의 row 가 업데이트되는 쿼리인 경우.

 

 

 

• 테스트 환경 준비

drop table test_emp;
/
create table test_emp
( emp_id number,
  first_name varchar2(30) 
);
/
drop table test_emp_sequence;

create table test_emp_sequence
( seq number,
 trigger_type varchar2(30)
 );
 
drop sequence s;

create sequence s;

 

 

 

• 4종류의 트리거 생성

--1  before statment
create or replace trigger before_insert_stat
before 
insert
on test_emp
begin
    insert into test_emp_sequence values (s.nextval,'before_insert_stat');
end;
/

--2  before  each row
create or replace trigger before_insert_each_row
before 
insert
on test_emp
for each row
begin
    insert into test_emp_sequence values (s.nextval,'before_insert_each_row');
end;
/

--3 after each row
create or replace trigger after_insert_each_row
after 
insert
on test_emp
for each row
begin
    insert into test_emp_sequence values (s.nextval,'after_insert_each_row');
end;
/
 
--4  after statment
create or replace trigger after_insert_stat
after 
insert
on test_emp
begin
    insert into test_emp_sequence values (s.nextval,'after_insert_stat');
end;

 

 

 

• 이벤트의 개수별 트리거 발생 비교

-- 1개의 이벤트가 발생한 경우
insert into  test_emp
values (1,'mazen');

select * from test_emp_sequence
order by seq;
-------------------------------
       SEQ TRIGGER_TYPE                  
---------- ------------------------------
         1 before_insert_stat            
         2 before_insert_each_row        
         3 after_insert_each_row         
         4 after_insert_stat


-- 2개의 이벤트가 발생한 경우
insert into  test_emp
select employee_id, first_name
from
employees
where department_id = 20;

select * from test_emp_sequence
order by seq;
-------------------------------
       SEQ TRIGGER_TYPE                  
---------- ------------------------------
         1 before_insert_stat            
         2 before_insert_each_row        
         3 after_insert_each_row         
         4 after_insert_stat             
         5 before_insert_stat            
         6 before_insert_each_row        
         7 before_insert_each_row        
         8 after_insert_each_row         
         9 after_insert_each_row         
        10 after_insert_stat

 

 

 

• Compile/enable/ disable/ drop trigger

--to compile trigger
alter trigger after_insert_each_row compile;


--to disable all triggers on a table
alter table test_emp disable all triggers;

select table_name, trigger_name, trigger_type, status 
from user_triggers
where table_name='TEST_EMP';
--------------------------------------------------------
TABLE_NAME TRIGGER_NAME                   TRIGGER_TYPE     STATUS  
---------- ------------------------------ ---------------- --------
TEST_EMP   BEFORE_INSERT_STAT             BEFORE STATEMENT DISABLED
TEST_EMP   BEFORE_INSERT_EACH_ROW         BEFORE EACH ROW  DISABLED
TEST_EMP   AFTER_INSERT_EACH_ROW          AFTER EACH ROW   DISABLED
TEST_EMP   AFTER_INSERT_STAT              AFTER STATEMENT  DISABLED


--to enbale all triggers on a table
alter table test_emp enable all triggers;

select table_name, trigger_name, trigger_type, status 
from user_triggers
where table_name='TEST_EMP';
--------------------------------------------------------
TABLE_NAME TRIGGER_NAME                   TRIGGER_TYPE     STATUS  
---------- ------------------------------ ---------------- --------
TEST_EMP   BEFORE_INSERT_STAT             BEFORE STATEMENT ENABLED 
TEST_EMP   BEFORE_INSERT_EACH_ROW         BEFORE EACH ROW  ENABLED 
TEST_EMP   AFTER_INSERT_EACH_ROW          AFTER EACH ROW   ENABLED 
TEST_EMP   AFTER_INSERT_STAT              AFTER STATEMENT  ENABLED 


--to disable or enable specific trigger
alter trigger after_insert_stat enable;
alter trigger after_insert_stat disable;


--to drop trigger
drop trigger after_insert_stat;