MSSQL insert, update, delete 동시 Trigger 사용법

하나의 Trigger에 insert, update, delete를 동시에 사용할 수 있는 Trigger 공유하려고 합니다.

 

Trigger를 사용하는 이유는?

• Trigger를 자주 사용하는 이유 중 하나는 테이블에서 변경 사항에 대한 로그를 다른 테이블에도 저장함으로써 무결성을 유지 위함입니다.
• 그런데 insert, update, delete에 대해서 각각 하나씩 Trigger를 생성할 필요는 없다고 생각합니다.
• 유지보수를 위해서라도 한계로 만들어서 관리해야 한다고 생각합니다.
• 물론 단점도 있는데 발생한 이벤트를 무엇인지 구별해서 로직을 추가해야 합니다.

INSERT, DELETE, UPDATE 각각의 이벤트.

• INSERT 이벤트: INTESTED  발생함, DELETED 발생하지 않음.
• DELETE 이벤트: INTESTED 발생하지 않음, DELETED 발생함.
• UPDATE 이벤트: INTESTED 발생함, DELETED 발생함.

insert, update, delete 동시 사용법.

insert, update, delete 동시 사용법 설명.

nsert, update, delete 동시 소스 및 사용법 설명.

insert, update, delete 동시 소스 코드.

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

DROP TABLE IF EXISTS AurumGuide_tr_multi_log;
CREATE TABLE AurumGuide_tr_multi_log (
    Aurum_id  int identity(1,1) NOT NULL,
    Aurum_Event        VARCHAR(255)   NULL 
);
go
CREATE OR ALTER TRIGGER TR_AurumGuide_multi_process 
ON AurumGuide_tr_multi
FOR INSERT, UPDATE, DELETE
AS  
IF EXISTS (SELECT 1 FROM inserted)
BEGIN
    IF EXISTS (SELECT 1 FROM deleted)
    BEGIN 
        INSERT INTO AurumGuide_tr_multi_log(Aurum_Event)
        VALUES('AurumGuide_UPDATE'); 
    END 
    ELSE
    BEGIN
        INSERT INTO AurumGuide_tr_multi_log(Aurum_Event) 
        VALUES('AurumGuide_INSERT');       
    END
END 
ELSE 
BEGIN
    INSERT INTO AurumGuide_tr_multi_log(Aurum_Event)
    VALUES('AurumGuide_DELETE');   
END;
 
--------------------------------
-- insert 
INSERT INTO dbo.AurumGuide_tr_multi
       (AurumId,AurumNm) 
VALUES (272, N'Ken')
      ,(273, N'Brian');
 
-- update 
update dbo.AurumGuide_tr_multi
    set AurumNm = 'up_AurumNm'
  where AurumId ='272';

-- DELETE 
DELETE 
  FROM  dbo.AurumGuide_tr_multi
WHERE  AurumId ='273';

-- CHECK THE DATA 
SELECT *
FROM AurumGuide_tr_multi_log;