테이블에 Insert, Update, Delete 등의 데이터 변화가 생길경우 이 이벤트를 감지해 주는 역할을 하는것이 트리거입니다. 즉, 삽입, 수정, 삭제등의 데이터조작이 발생할 경우 이를 감지하여 이를 어떠한 특수목적을 가지고 부가적인 처리를 할 수 있다는 것입니다.
MSSQL Trigger 사용방법
어떤때에 트리거를 사용하나??
트리거는 다양한 용도로 사용할 수 있는데 보통은 데이터의 입력,수정,삭제등의 이벤트에 따른 로그기록, 예를 들면 언제 지워졌고, 지워진 데이터가 무엇인지를 로그 테이블에 기록하거나 특정테이블에 데이터 변화가 감지되면 다른테이블의 데이터도 조작을 해야하는 경우, 그리고 데이터가 테이블에 기록되기전에 데이터 무결성 체크와 유효성 체크등이 필요한 경우 등에 사용될 수 있습니다.
MSSQL Trigger를 이용한 로그테이블 사용해 보기
테스트를 위해 아래와 같이 2개의 테이블을 만들었습니다. TestA 테이블에 데이터를 인서트,업데이트,딜리트 할 것이고, TestB 테이블에는 트리거를 이용한 로그를 남길 것입니다.
트리거 테스트를 위한 샘플 테이블 생성
CREATE TABLE [dbo].[TestA](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[userSerialNo] [varchar](10) NOT NULL,
[userName] [varchar](20) NOT NULL,
[userOld] [int] NULL,
[userPhone] [varchar](20) NULL,
[userMemo] [varchar](100) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TestB](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[userSerialNo] [varchar](10) NOT NULL,
[userName] [varchar](20) NOT NULL,
[userOld] [int] NULL,
[userPhone] [varchar](20) NULL,
[userMemo] [varchar](100) NULL,
[trgType] [varchar](1) NOT NULL
) ON [PRIMARY]
GO
테이블을 모두 만들었으면 트리거도 만들어 줘야 합니다. SSMS 에서 손쉽게 마우스를 클릭해서 새 트리거 메뉴를 이용해 만들어 줍니다. 트리거는 TestA 테이블에 만들어줍니다.
MSSQL Trigger 생성
아래와 같은 기본 구문이 자동으로 생성되는데 트리거 명칭과 트리거가 적용될 테이블명을 지정해주면 됩니다.
-- SSMS 기본 생성 트리거 SQL 구문
--
CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name>
ON <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name>
AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
END
GO
트리거 생성시 주의사항
테이블에 인서트가 됐을 경우 inserted 라는 테이블을 통해 인서트된 데이터에 접근이 가능합니다. 또한, delete의 경우 deleted 테이블에 접근해 삭제된 데이터에 접근이 가능하며, updated 라는 테이블은 없는데 이는 SQL UPDATE의 개념이 삭제 후 업데이트 이기 때문에 inserted 를 참조하면됩니다.
실제 생성할 트리거의 쿼리문입니다.
-- 테스트용 생성 트리거
--
CREATE TRIGGER dbo.TRG_TESTA_IUD_TEST
ON dbo.TestA
AFTER INSERT,DELETE,UPDATE --트리거 수행이 인서트,딜리트,업데이트 이후에 동작한다는 뜻입니다.
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Action As CHAR(1) -- 인서트인지 업데이트인지 삭제인지 구분할 변수
SET @Action = 'I' --기본 인서트 플래그
-- deleted 테이블에 데이터가 있고 inserted에도 데이터가 있으면 UPDATE 된것으로 판단
-- deleted 테이블에 데이터가 없으면 인서트로 판단
IF Exists(select * from deleted)
begin
SET @Action = (Case when Exists(select * from inserted) then 'U' else 'D' End)
end
Declare @userSerialNo As Varchar(10)
Declare @userName As Varchar(20)
Declare @userOld As Int
Declare @userPhone As Varchar(20)
Declare @userMemo As Varchar(100)
-- insert, update, delete를 구분하여 해당 테이블에서 데이터를 가져와 변수에 할당
IF @Action = 'D'
Begin
select @userSerialNo=userSerialNo, @userName=userName, @userOld=userOld,
@userPhone=userPhone, @userMemo=userMemo
from deleted --삭제된 테이블의 알리아스라고 생각하면됨
End
Else
Begin
select @userSerialNo=userSerialNo, @userName=userName, @userOld=userOld,
@userPhone=userPhone, @userMemo=userMemo
from inserted --인서트 또는 삭제된 테이블의 알리아스라고 생각하면됨
End
-- 로그테이블인 TestB 에 기록
Insert into TestB (userSerialNo, userName, userOld, userPhone, userMemo, trgType)
values (@userSerialNo, @userName, @userOld, @userPhone, @userMemo, @Action)
END
GO
트리거가 생성되었으니 TestA 테이블에 인서트, 업데이트, 삭제 등의 데이터 조작을 해보고 정상적인 트리거 동작을 했는지 TestB 테이블을 조회하여 데이터를 검색해 봅니다.
트리거를 이용한 이력 생성
TestB.trgType 필드에 I, U, D 등의 플래그가 표시되는 것을 확인 할 수 있습니다. MSSQL 트리거 사용법을 이해하는데 도움이 되셨길 바랍니다.
