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

MSSQL AFTER Trigger는 Trigger가 설정된 테이블 또는 뷰에서 INSET, UPDATE, DELETE DML 문 실행 후 Trigger가 자동으로 실행되는 저장 프로시저입니다.

AFTER Trigger 특징.

• Trigger가 설정된 테이블 또는 뷰에서 INSET, UPDATE, DELETE DML 문 실행 후 Trigger가 실행됩니다.
• 예를 들면 테이블에 INSERT 문이 해당 테이블에 데이터를 입력 후 Trigger의 T-SQL 문이 실행됩니다.
• 사용자가 한 개의 테이블에 입력했지만, Trigger를 이용하여 다수의 테이블에 DATA를 변경할 수 있는 장점이 있습니다.
• 웹사이트에 로그인할 때 로그인 정보 이력을 변경하고 관련 테이블도 자동으로 추가할 수 있습니다.

AFTER Trigger 사용법.

AFTER Trigger 생성.

Trigger delete, insert 동시 정보.

DML Trigger AFTER UPDATE 데이터 설명 입니다.

 

Trigger AFTER INSERT.

DROP TABLE IF EXISTS AurumGuide_DML_TR_AFTER_OPTION;
-- Create Sample Table 
CREATE TABLE AurumGuide_DML_TR_AFTER_OPTION (
    AurumId           INT NOT NULL,
    AurumNm           VARCHAR(255) NOT NULL,
    AurumAge          INT  NULL,
    AurumAddress      VARCHAR(500)  NULL
);
go
 
DROP TABLE IF EXISTS AurumGuide_DML_TR_AFTER_OPTION_LOG; 
CREATE TABLE AurumGuide_DML_TR_AFTER_OPTION_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_TR_AFTER_INSERT
CREATE TRIGGER dbo.TR_AurumGuide_DML_TR_AFTER_INSERT
ON dbo.AurumGuide_DML_TR_AFTER_OPTION
AFTER INSERT
AS
INSERT INTO dbo.AurumGuide_DML_TR_AFTER_OPTION_LOG
		(AurumId,AurumNm, AurumEvent, AurumDateTime)
SELECT AurumId,AurumNm,'INSERT', GETDATE() FROM inserted;
-- excute INSERT
INSERT INTO dbo.AurumGuide_DML_TR_AFTER_OPTION(AurumId,AurumNm) 
VALUES (272, N'Ken')
      ,(273, N'Brian') ;

-- check Data
SELECT * FROM AurumGuide_DML_TR_AFTER_OPTION;
-- check Data
SELECT * FROM AurumGuide_DML_TR_AFTER_OPTION_LOG;

Trigger AFTER UPDATE.

CREATE TRIGGER dbo.TR_AurumGuide_DML_TR_AFTER_UPDATE
ON dbo.AurumGuide_DML_TR_AFTER_OPTION
AFTER UPDATE
AS
INSERT INTO dbo.AurumGuide_DML_TR_AFTER_OPTION_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_DML_TR_AFTER_OPTION
	SET AurumId = '373'
       ,AurumNm = 'TR NAME'
	WHERE AurumId = 273;
-- check Data
SELECT * FROM AurumGuide_DML_TR_AFTER_OPTION;
SELECT * FROM AurumGuide_DML_TR_AFTER_OPTION_LOG;

Trigger AFTER DELETE.

CREATE TRIGGER dbo.TR_AurumGuide_DML_TR_AFTER_DELETE
ON dbo.AurumGuide_DML_TR_AFTER_OPTION
AFTER DELETE
AS
INSERT INTO dbo.AurumGuide_DML_TR_AFTER_OPTION_LOG
		(AurumId,AurumNm, AurumEvent, AurumDateTime)
SELECT AurumId,AurumNm,'before_DELETE', GETDATE() 
 FROM deleted;
 -- delete
delete
 from AurumGuide_DML_TR_AFTER_OPTION 
 where AurumId = 272;
-- check Data
SELECT * FROM AurumGuide_DML_TR_AFTER_OPTION;
SELECT * FROM AurumGuide_DML_TR_AFTER_OPTION_LOG;

AFTER Trigger 수정.

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

-- ALTER TRIGGER
ALTER TRIGGER dbo.TR_AurumGuide_DML_TR_AFTER_INSERT
ON dbo.AurumGuide_DML_TR_AFTER_OPTION
AFTER INSERT
AS
INSERT INTO dbo.AurumGuide_DML_TR_AFTER_OPTION_LOG
 (AurumId,AurumNm, AurumEvent, AurumDateTime)
SELECT AurumId,AurumNm,'INSERT', GETDATE() FROM inserted
UNION ALL
SELECT 100,'AurumGuide','INSERT-ALTER', GETDATE() 
;
-- insert
INSERT INTO dbo.AurumGuide_DML_TR_AFTER_OPTION(AurumId,AurumNm) 
VALUES (272, N'Ken')
      ,(273, N'Brian') ;
-- check Data
SELECT * FROM AurumGuide_DML_TR_AFTER_OPTION;
SELECT * FROM AurumGuide_DML_TR_AFTER_OPTION_LOG;

AFTER Trigger 삭제.

생성된 Trigger 삭제합니다.

-- 삭제 
DROP TRIGGER dbo.TR_AurumGuide_DML_TR_AFTER_INSERT;
DROP TRIGGER dbo.TR_AurumGuide_DML_TR_AFTER_UPDATE;
DROP TRIGGER dbo.TR_AurumGuide_DML_TR_AFTER_DELETE;