MSSQL DML Trigger 특징 및 유형

DML Trigger는 테이블 또는 뷰에서 변경 상황이 발생할 때 변경되는 데이터를 자동으로 확인해 줄 수 있는 저장 프로시저입니다.

DML Trigger 특징.

• DML 이벤트에는 INSERT, UPDATE 또는 DELETE 문이 포함됩니다.
• DML Trigger를 사용하여 비즈니스 규칙 및 데이터 무결성을 적용하고, 다른 테이블을 Query 하고, 복잡한 Transact-SQL 문을 포함할 수 있습니다. 
• CHECK 제약 조건으로 정의된 것보다 더 복잡하게 INSERT, UPDATE 및 DELETE 문에 제한을 적용할 수 있습니다.
• DML Trigger는 데이터 수정 전후의 테이블 상태를 체크하고 해당 차이점에 따라 전혀 다른 작업을 수행할 수 있습니다.
• 하나의 테이블에 DML Trigger를 여러 개 만들면 같은 수정문이 실행될 때 여러 다른 동작을 실행할 수 있습니다.
• DML Trigger는 참조 무결성을 위반하는 변경 내용을 허용하지 않거나 rollback 되는 data를 다른 테이블에 저장해서 분석할 수 있습니다.

DML Trigger 문법.

Syntax.

CREATE TRIGGER [schema_name.]trigger_name
ON { table_name | view_name }
{ AFTER | INSTEAD OF } {[INSERT],[UPDATE],[DELETE]}
[NOT FOR REPLICATION]
AS
    {Tsql- 문장}

Syntax 설명.

• schema_name은 스키마를 지정합니다. 기본값은 DBO입니다.
• trigger_name에는 생성하는 Trigger 이름을 지정합니다.
• ON { table_name | view_name }은 DML Trigger를 생성되는 테이블 또는 view 이름입니다.
• AFTER 절은 Trigger를 발생시킬 INSERT, UPDATE 또는 DELETE 이벤트를 지정합니다. 
• AFTER 절은 SQL Server에서 DML 문 실행을 성공적으로 완료한 후에만 Trigger가 시작되는 옵션입니다.
• INSTEAD OF 절은 INSERT, UPDATE 또는 DELETE 문을 테이블로 건너뛰고 대신 Trigger에 정의된 다른 문을 실행하는 데 사용됩니다. 
• 따라서 실제 INSERT, UPDATE 또는 DELETE 문은 전혀 발생하지 않습니다.
• [NOT FOR REPLICATION] 절은 REPLICATION 에이전트가 테이블을 수정할 때 Trigger를 호출하지 않도록 SQL Server에 알려주는 옵션입니다.
• {Tsql- 문장} 부분은 이벤트가 발생할 때 Trigger에서 실행되는 T-SQL 문장입니다.

DML Trigger 유형.

AFTER Trigger.

AFTER Trigger는 삽입, 업데이트 또는 삭제와 같은 Trigger 동작 다음에 실행됩니다.

소스코드입니다.

--- AFTER TRIGGER 소스코드.
USE sampleDB;
DROP TABLE IF EXISTS AurumGuide_DML_AFTER_TR;
-- Create Sample Table 
CREATE TABLE AurumGuide_DML_AFTER_TR (
    AurumId           INT NOT NULL,
    AurumNm           VARCHAR(255) NOT NULL,
    AurumAge          INT  NULL,
    AurumAddress      VARCHAR(500)  NULL
);
-- Create Sample Data
INSERT INTO dbo.AurumGuide_DML_AFTER_TR(AurumId,AurumNm) 
VALUES (272, N'Ken')
      ,(273, N'Brian')
      ,(274, N'Stephen')
      ,(275, N'Michael')
      ,(276, N'Linda');

DROP TABLE IF EXISTS AurumGuide_DML_AFTER_TR_LOG; 
CREATE TABLE AurumGuide_DML_AFTER_TR_LOG (
    AurumId           INT NOT NULL,
	AurumNm           VARCHAR(255)  NULL,
    AurumEvent        VARCHAR(255)  NULL,
    AurumDateTime          datetime  NULL     
);
 go
-- Example: AFTER Trigger
-- DROP TRIGGER dbo.TR_AurumGuide_DML_AFTER
CREATE TRIGGER dbo.TR_AurumGuide_DML_AFTER
ON dbo.AurumGuide_DML_AFTER_TR
AFTER UPDATE
AS
INSERT INTO dbo.AurumGuide_DML_AFTER_TR_LOG
		(AurumId,AurumNm, AurumEvent, AurumDateTime)
SELECT AurumId,AurumNm,'UPDATE', GETDATE() FROM DELETED;
go
--update
update AurumGuide_DML_AFTER_TR
  set AurumId = '373'
     ,AurumNm = 'TR NAME'
  where AurumId = 273;

-- UPDATE 테이블 확인
SELECT * FROM AurumGuide_DML_AFTER_TR;
-- LOG 테이블확인
SELECT * FROM  AurumGuide_DML_AFTER_TR_LOG;

 

소스 설명입니다.

AFTER TRIGGER 소스 코드 설명 입니다.

INSTEAD OF Trigger.

• INSTEAD OF Trigger는 Trigger 지정한 T-SQL의 테이블 대신 실행됩니다. 
• 예를 들어 AurumGuide 테이블에 INSTEAD OF UPDATE Trigger가 있고 AurumGuide 테이블에 대해 UPDATE 문이 실행되면 UPDATE 문은 AurumGuide 테이블의 행을 변경하지 않습니다. 
• 즉, UPDATE 문을 사용하면 INSTEAD OF UPDATE Trigger가 실행되어 Tsql 지정한 AurumGuide_LOG 테이블에 변화가 발생합니다.

소스코드.

-- INSTEAD OF Triggers
GO
USE sampleDB;
DROP TABLE IF EXISTS AurumGuide_DML_INSTEAD_TR;
-- Create Sample Table 
CREATE TABLE AurumGuide_DML_INSTEAD_TR (
    AurumId           INT NOT NULL,
    AurumNm           VARCHAR(255) NOT NULL,
    AurumAge          INT  NULL,
    AurumAddress      VARCHAR(500)  NULL
);
GO
-- Create Sample Data
INSERT INTO dbo.AurumGuide_DML_INSTEAD_TR(AurumId,AurumNm) 
VALUES (272, N'Ken')
      ,(273, N'Brian')
      ,(274, N'Stephen')
      ,(275, N'Michael')
      ,(276, N'Linda');
GO
DROP TABLE IF EXISTS AurumGuide_DML_INSTEAD_TR_LOG; 
CREATE TABLE AurumGuide_DML_INSTEAD_TR_LOG (
  AurumId           INT NOT NULL,
  AurumNm           VARCHAR(255)  NULL,
  AurumEvent        VARCHAR(255)  NULL,
  AurumDateTime     datetime  NULL     
);

 go
 DROP TRIGGER dbo.TR_AurumGuide_DML_INSTEAD;
 GO
 -- Example: AFTER Trigger
CREATE TRIGGER dbo.TR_AurumGuide_DML_INSTEAD
ON dbo.AurumGuide_DML_INSTEAD_TR
INSTEAD OF DELETE
AS
INSERT INTO dbo.AurumGuide_DML_INSTEAD_TR_LOG
(AurumId,AurumNm, AurumEvent, AurumDateTime)
SELECT AurumId,AurumNm,'UPDATE', GETDATE() FROM DELETED;

--DELETE
DELETE FROM DBO.AurumGuide_DML_INSTEAD_TR;  

-- DELETE 테이블 확인
SELECT * FROM DBO.AurumGuide_DML_INSTEAD_TR;

-- LOG 테이블확인
SELECT * FROM DBO.AurumGuide_DML_INSTEAD_TR_LOG;

 

소스 설명.

INSTEAD OF Triggers 설명 입니다.

FOR Trigger.

• FOR Trigger에 대해서 질문하는 개발자, DBA 있어서 간단하게 설명하겠습니다.
• 결론부터 이야기하면 FOR Trigger는 AFTER Trigger 차이점이 없다고 보시면 됩니다.
• SQL Server 2000 이전에는 FOR Trigger를 사용할 수 있는 유일한 유형의 Trigger로 지금도 Syntax에서 나와 있습니다.
• SQL Server 2000 이전 버전 사용자가 아니라면 AFTER Trigger 사용하시면 됩니다.