트리거를 사용하여 특정 컬럼 업데이트 하기 (After 트리거)
- Version : SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016
트리거는 특정 이벤트가 발생(INSERT, UPDATE, DELETE)할 때 자동적으로 실행되는 특수한 종류의 저장프로시저이다.
트리거는 FOR|AFTER와 INSTEAD OF 인수가 있다.
- FOR|AFTER : AFTER는 DML 트리거를 지정한 모든 작업이 성공적으로 실행되었을 때만 트거가 실행도록 지정한다. 모든 참조 연계 동작 및 제약 조건 검사도 이 트리거가 실행되기 전에 성공해야 한다.
- INSTEAD OF : 트리거를 시작하는 SQL문 대신 DML 트리거가 실행되도록 지정한다. DDL 또는 LOGON 트리거에 대해서는 INSTEAD OF를 지정할 수 없다.
AFTER 트리거는 요청이 정상적으로 완료 되었을 때 이벤트가 실행 된다. After 트리거에 대해서 실습을 통해 알아본다.
After 트리거 실습을 위해 테스트 테이블 및 데이터를 생성한다.
create table tblTriggerTest ( pkID integer Identity(1,1) primary key, ExecuteTime datetime, TriggerTime datetime, OrderStatus varchar(20) )
insert into tblTriggerTest values (NULL,NULL, 'Pending') insert into tblTriggerTest values (NULL,NULL, 'Pending') insert into tblTriggerTest values (NULL,NULL, 'Pending')
select * from tblTriggerTest go |
트리거를 생성한다. 생성할 트리거는 tblTriggerTest 테이블에 업데이트가 발생하면 해당 작업 완료 후 트리거가 실행되어 현재 이벤트가 발생한 시간을 특정 컬럼(TriggerTime)에 업데이트하도록 되어있다.
create trigger trTriggerTest on tblTriggerTest after update as begin update tblTriggerTest set TriggerTime=getdate() from tblTriggerTest t inner join inserted i on t.pkID=i.pkID and i.OrderStatus='Approved' end go |
데이터를 업데이트하고 조회한다. OrderApprovalDateTime 테이블에 트리거가 발생한 시간이 업데이트 된 것을 확인할 수 있다.
update tblTriggerTest set ExecuteTime = getdate(), OrderStatus='Approved' where pkID=1 go
select * from tblTriggerTest go |
After 트리거로 업데이트 요청이 완료되면 실행 되기 때문에 ExecuteTime과 TriggerTime에 차이가 발생한 것을 확인할 수 있다.
AFTER 트리거를 INSTEAD OF 트리거로 변경 후 효율성을 향상시킬 수 있는 방법에 대해서도 참고하면 좋을 듯 하다.
- AFTER 트리거를 INSTEAD OF 트리거로 변경 후 효율성 향상
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
