MSSQL INSTEAD OF Trigger 생성, 수정, 삭제 사용법

INSTEAD OF Trigger 옵션을 사용하면은 테이블에 insert를 실행하면 해당 테이블에 insert 되는 게 아니라 Trigger의 실행문에서 사용하는 테이블에 insert 발생합니다.

INSTEAD OF Trigger 특징.

INSTEAD OF Trigger는 "Instead of Triggers" 정의할 수 있습니다.
INSTEAD OF Trigger는 테이블 또는 뷰에 대해 정의할 수 있습니다.
WITH CHECK OPTION으로 정의된 뷰에 대해 INSERT 문을 실행하면 SQLE_CHECK_TRIGGER_CONFLICT 오류가 발생합니다.
INSTEAD OF Trigger는 입력, 삭제, 수정할 수 없는 view의 data를 변경할 수 있습니다.
테이블에는 하나의 INSTEAD OF Trigger만 정의할 수 있습니다.
INSTEAD OF Trigger에는 ORDER 또는 WHEN 절을 사용할 수 없습니다.

INSTEAD OF Trigger 사용법.

INSTEAD OF Trigger 생성.

INSTEAD OF INSERT 소스코드

-- INSTEAD OF Triggers
GO
USE sampleDB;
DROP TABLE IF EXISTS AurumGuide_INSTEAD_OF_TR;
-- Create Sample Table 
CREATE TABLE AurumGuide_INSTEAD_OF_TR (
    AurumId           INT NOT NULL,
    AurumNm           VARCHAR(255) NOT NULL,
    AurumAge          INT  NULL,
    AurumAddress      VARCHAR(500)  NULL
);
GO

DROP TABLE IF EXISTS AurumGuide_INSTEAD_OF_TR_LOG; 
CREATE TABLE AurumGuide_INSTEAD_OF_TR_LOG (
    AurumId           INT NOT NULL,
    AurumNm           VARCHAR(255)  NULL,
    AurumEvent        VARCHAR(255)  NULL,
    AurumDateTime          datetime  NULL     
);
go
 ----------------------------------------------
 -- INSERT 
 DROP TRIGGER dbo.TR_AurumGuide_INSTEAD_OF_INSERT;
 GO
	 -- Example: AFTER Trigger
	CREATE TRIGGER dbo.TR_AurumGuide_INSTEAD_OF_INSERT
	ON dbo.AurumGuide_INSTEAD_OF_TR
	INSTEAD OF INSERT
	AS
	INSERT INTO dbo.AurumGuide_INSTEAD_OF_TR_LOG
	(AurumId,AurumNm, AurumEvent, AurumDateTime)
	SELECT AurumId,AurumNm,'Inserted', GETDATE() 
	FROM Inserted ;

-- insert data
INSERT INTO dbo.AurumGuide_INSTEAD_OF_TR
	    (AurumId,AurumNm) 
VALUES (272, N'Ken')
		,(273, N'Brian')
-- check data
SELECT * FROM AurumGuide_INSTEAD_OF_TR;
SELECT * FROM AurumGuide_INSTEAD_OF_TR_LOG;

INSTEAD OF INSERT TRIGGER 소스, 실행 DATA 확인입니다.

INSTEAD OF UPDATE 소스코드

 -- UPDATE 
DROP TRIGGER dbo.TR_AurumGuide_INSTEAD_OF_UPDATE;
GO 
CREATE TRIGGER dbo.TR_AurumGuide_INSTEAD_OF_UPDATE
ON dbo.AurumGuide_INSTEAD_OF_TR
INSTEAD OF UPDATE
AS
INSERT INTO dbo.AurumGuide_INSTEAD_OF_TR_LOG
(AurumId,AurumNm, AurumEvent, AurumDateTime)
SELECT AurumId,AurumNm,'UPDATE_deleted', GETDATE() 
FROM deleted 
UNION ALL
SELECT AurumId,AurumNm,'UPDATE_Inserted', GETDATE() 
FROM Inserted ;

--update
update AurumGuide_INSTEAD_OF_TR
	set AurumId = '373'
       ,AurumNm = 'TR NAME'
  where AurumId = 273;
-- check data 
SELECT * FROM AurumGuide_INSTEAD_OF_TR;
SELECT * FROM AurumGuide_INSTEAD_OF_TR_LOG;

INSTEAD OF DELETE 소스코드

 -- DELETE 
DROP TRIGGER dbo.TR_AurumGuide_INSTEAD_OF_DELETE;
GO 
CREATE TRIGGER dbo.TR_AurumGuide_INSTEAD_OF_DELETE
ON dbo.AurumGuide_INSTEAD_OF_TR
INSTEAD OF DELETE
AS
INSERT INTO dbo.AurumGuide_INSTEAD_OF_TR_LOG
(AurumId,AurumNm, AurumEvent, AurumDateTime)
SELECT AurumId,AurumNm,'deleted', GETDATE() 
FROM deleted;

--DELETE
DELETE FROM DBO.AurumGuide_INSTEAD_OF_TR;  
-- DELETE 테이블 확인
SELECT * FROM DBO.AurumGuide_INSTEAD_OF_TR;
-- LOG 테이블확인
SELECT * FROM DBO.AurumGuide_INSTEAD_OF_TR_LOG;

INSTEAD OF Trigger 수정.

ALTER Trigger 명령어로 생성된 Trigger를 수정합니다.

-- 트리거 수정
ALTER TRIGGER dbo.TR_AurumGuide_INSTEAD_OF_DELETE
ON dbo.AurumGuide_INSTEAD_OF_TR
INSTEAD OF DELETE
AS
-- 수정 부분
DELETE FROM dbo.AurumGuide_INSTEAD_OF_TR_LOG;

INSERT INTO dbo.AurumGuide_INSTEAD_OF_TR_LOG
(AurumId,AurumNm, AurumEvent, AurumDateTime)
SELECT AurumId,AurumNm,'deleted', GETDATE() 
FROM deleted;


--DELETE
DELETE FROM DBO.AurumGuide_INSTEAD_OF_TR;  
-- DELETE 테이블 확인
SELECT * FROM DBO.AurumGuide_INSTEAD_OF_TR;
-- LOG 테이블확인
SELECT * FROM DBO.AurumGuide_INSTEAD_OF_TR_LOG;

INSTEAD OF Trigger 삭제.

생성된 Trigger 삭제합니다.

-- 삭제 
DROP TRIGGER dbo.TR_AurumGuide_INSTEAD_OF_INSERT;
DROP TRIGGER dbo.TR_AurumGuide_INSTEAD_OF_UPDATE;
DROP TRIGGER dbo.TR_AurumGuide_INSTEAD_OF_DELETE;