MSSQL Trigger, 트리거 사용방법을 알아보자

오늘은 MSSQL Database의 트리거 사용방법을 알아보도록 하겠습니다.

 

트리거는 무엇에 쓰는 물건인고??

테이블에 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 트리거 사용법을 이해하는데 도움이 되셨길 바랍니다.