ORACLE 트리거 샘플

트리거 (TRIGGER)

 

트리거는 INSERT, UPDATE, DELETE문이 TABLE에 대해 행해질 때 자동으로 수행되는 PROCEDURE 로, TABLE과는 별도로 DATABASE에 저장 되며, 중요한건 트리거는 테이블에 대해서만 정의 될 수 있음

트리거 시작 유형
    - BEFORE : INSERT, UPDATE, DELETE문이 실행되기 전에 트리거가 실행
    - AFTER : INSERT, UPDATE, DELETE문이 실행된 후 트리거가 실행

 

트리거 이벤트 진행
    - INSERT, UPDATE, DELETE 중에서 한 개 이상 올 수 있음

 

트리거 대상 유형
    - FOR EACH ROW  : 행 트리거 기준 (컬럼의 각각의 행의 데이터 행 변화가 생길때마다 실행)
    - FOR STATEMENT : 문장 트리거 (트리거 사건에 의해 단 한번 실행되며, 컬럼의 각 데이터 행을 제어할수 없음)

 

레퍼런스 별칭
    REFERENCING NEW AS new OLD AS old
    old.칼럼.값 = 트리거 이전.칼럼.값
    new.칼럼.값 = 트리거 이후.칼럼.값

 

트리거 샘플
  create or replace trigger user.trigger_name
  after delete or insert or update
  on user.from_table_name
  referencing

    new as new

    old as old
  for each row
  --------------- PL/SQL 바디 시작 ---------------

  ....

 

 

 

 

 

 

1. 원본 테이블의 스키마 정의

 

create table demo_tbl1
(
  id                     varchar2(50 byte)      not null,
  status                 varchar2(13 byte)      not null,
  process_id             varchar2(71 byte)      not null,
  activity_id            varchar2(60 byte)      not null,
  activity_name          varchar2(80 byte),
  activity_starttime     date,
  activity_completetime  date,
  activity_status        varchar2(10 byte),
  subprocess_id          varchar2(34 byte),
  errorcode              number(5)
)

 

 

 

2. 대상 테이블의 스키마 정의

 

create table demo_tbl_mapp1 
(
    id                    varchar2(50 byte)       not null,
    status                varchar2(13 byte)       not null,
    process_id            varchar2(71 byte)       not null,
    activity_id           varchar2(60 byte)       not null,
    activity_name         varchar2(80 byte),
    activity_starttime    date,
    activity_completetime date,
    activity_status       varchar2(10 byte),
    subprocess_id         varchar2(34 byte),
    errorcode             number(5),
    action_code           char(1),  -- Create,Update,Delete
    trigger_date          char(12)  default to_char(sysdate,'yyyymmddhh24mi'), -- 트리거일시
    trigger_time          date        default sysdate -- 트리거시간
)

 

 

 

3. 트리거의 생성

create or replace trigger sin.demo_trigger_mapp1
    after 
        delete or insert or update
    on 
        sin.demo_tbl1 
referencing
    new as new 
    old as old
for 
    each row
declare
/******************************************************************************
   name:       demo_trigger_mapp1
   purpose:   

   revisions:
   ver        date        author           description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2006-07-27             1. created this trigger.

   notes:
      object name:     demo_trigger_mapp1
      sysdate:         2006-07-27
      date and time:   2006-07-27, 오전 10:53:59, and 2006-07-27 오전 10:53:59
      username:        lamp
      table name:      demo_tbl1
      trigger options: 
******************************************************************************/

begin

    if inserting then
        insert into demo_tbl_mapp1 (
            id,
            status,
            process_id,
            activity_id,
            activity_name,
            activity_starttime,
            activity_completetime,
            activity_status,
            subprocess_id,
            errorcode,
            action_code
        ) 
        values (
            :new.id,
            :new.status,
            :new.process_id,
            :new.activity_id,
            :new.activity_name,
            :new.activity_starttime,
            :new.activity_completetime,
            :new.activity_status,
            :new.subprocess_id,
            :new.errorcode,
            'c'); 
            
    elsif updating then    
        insert into demo_tbl_mapp1 (
            id,
            status,
            process_id,
            activity_id,
            activity_name,
            activity_starttime,
            activity_completetime,
            activity_status,
            subprocess_id,
            errorcode,
            action_code
        ) 
        values (
            :new.id,
            :new.status,
            :new.process_id,
            :new.activity_id,
            :new.activity_name,
            :new.activity_starttime,
            :new.activity_completetime,
            :new.activity_status,
            :new.subprocess_id,
            :new.errorcode,
            'u' );
            
    elsif deleting then
        insert into demo_tbl_mapp1 (
            id,
            status,
            process_id,
            activity_id,
            activity_name,
            action_code
        ) 
        values (
            :old.id,
            :old.status,
            :old.process_id,
            :old.activity_id,
            :old.activity_name,
            'd' );
            
    end if;

exception
    when others then
        -- consider logging the error and then re-raise
    raise;
end demo_trigger_mapp1;
/

 

 

 

4. 트리거 실행

 

-- 입력시 트리거 모니터

insert into demo_tbl1 (
    id,
    status,
    process_id,
    activity_id,
    activity_name,
    activity_starttime,
    activity_completetime,
    activity_status,
    subprocess_id,
    errorcode
) 
values (
    'BIZPUS_TRIGGER_DEMO1',
    'TRIGGER_START',
    'PROCESS_1',
    'activity_1',
    '트리거 ACT',
    sysdate,
    sysdate,
    'BEGIN',
    'SUB_PROCESS_1',
    1 );

 

   

-- 업데이트시 트리거 모니터

update demo_tbl1 set
    activity_id = 'u_activity_1'
where 
    id = 'BIZPUS_TRIGGER_DEMO1'
    and status = 'TRIGGER_START'
    and process_id = 'PROCESS_1'

 

 

 

-- 삭제시 트리거 모니터

delete from demo_tbl1 
where 
    id = 'BIZPUS_TRIGGER_DEMO1'
    and status = 'TRIGGER_START'
    and process_id = 'PROCESS_1'

 

-- 모니터 쿼리

select * from demo_tbl_mapp1 where trigger_date like to_char(sysdate, 'YYYYMMDDHH24') || '%'

출처 : http://cafe.naver.com/cuteshim/398

====================================================================================================

질문은 '특정 테이블에 정의되어 있는 각각의 insert, update, delete 트리거를 Oracle 처럼 하나로 합칠 수 있는가?' 입니다.

 

참고로, Oracle에서의 트리거는 INSERTING, UPDATING, DELETING이라는 키워드를 조건문(IF INSERTING)에 사용하여, 각 트리거의 로직을 하나의 트리거에서 각각 처리 할 수 있지만, SQL 서버에서는 아직 이러한 키워드가 존재하지 않는 것 같습니다.(아니면, 제가 못 찾았을지도 모르죠) 그래서, 다음과 같은 방법을 생각해보았습니다.

 

create table dbo.T1 -- 트리거가 발생되는 테이블

(c1 int, c2 int)

go

 

create table dbo.T2 -- 트리거에 의해 변경되는 테이블

(c1_old int, c1_new int, c2_old int, c2_new int, job char(20))

go

 

create trigger trig_T1
on dbo.T1
after insert, update, delete
as
declare @i int, @d int
declare @c1 int, @c2 int
select @i=count(*) from inserted
select @d=count(*) from deleted
if (@d=0)  -- deleted에 한건도 존재하지 않으면 insert 트리거
begin
     insert into dbo.T2 (c1_new, c2_new, job) select c1, c2, 'INSERTING' from inserted
end
else
begin
     if (@i=0) -- inserted에 한건도 존재하지 않으면 delete 트리거
     begin
          insert into dbo.T2(c1_old, c2_old, job) select c1, c2, 'DELETING' from deleted
     end
     else
     begin
          if (@i<>0 and @d<>0) -- inserted와 deleted에 행이 둘다 존재하면 update 트리거
          begin
              insert into dbo.T2(c1_old, c1_new, c2_old, c2_new, job)
                  select del.c1, ins.c1, del.c2, ins.c2, 'UPDATING' from inserted as ins cross join deleted as del
          end
     end
end

 

다음과 같이 트리거를 테스트 합니다.

 

insert into dbo.T1 values(1, 1)
update dbo.T1 set c2=c2+1 where c1=1
delete from dbo.T1 where c1=1

 

그 결과를 확인합니다.

select * from dbo.T2

                 

c1_old         c1_new       c2_old        c2_new       job
----------- ----------- ----------- ----------- --------------------
NULL          1                 NULL         1                INSERTING           
1                1                 1               2                UPDATING            
1                NULL           2               NULL          DELETING 

 

출처 : http://cafe.naver.com/tech2u/111