AFTER 트리거, INSTEAD OF 트리거

AFTER 트리거와 INSTEAD OF 트리거

 

-       Version : SQL Server 2005, 2008, 2008R2, 2012 

 

트리거는 SQL Server에서 이벤트가 발생하면 자동으로 실행되는 특수한 종류의 저장 프로시저이다. DML, DDL, LOGON 트리거를 생성 할 수 있다.  

 

트리거는 FOR|AFTER 와 INSTEAD OF 인수가 있다. 

  FOR|AFTER : AFTER는 DML 트리거를 지정한 모든 작업이 성공적으로 실행되었을 때만 트거가 실행도록 지정한다. 모든 참조 연계 동작 및 제약 조건 검사도 이 트리거가 실행되기 전에 성공해야 한다.  
  INSTEAD OF : 트리거를 시작하는 SQL문 대신 DML 트리거가 실행되도록 지정한다. DDL 또는 LOGON 트리거에 대해서는 INSTEAD OF를 지정할 수 없다. 

 

 

실습을 위해 트리거를 기록할 테이블을 생성한다. 

CREATE TABLE dbo.UserNames_After  (    ID INT IDENTITY(1,1) PRIMARY KEY,    Name NVARCHAR(255) NOT NULL UNIQUE  );    CREATE TABLE dbo.UserNames_InsteadOf  (    ID INT IDENTITY(1,1) PRIMARY KEY,    Name NVARCHAR(255) NOT NULL UNIQUE  );

 

예외 목록을 저장할 테이블을 생성 후 예외 목록을 입력 한다. 

CREATE TABLE dbo.NaughtyUserNames  (    Name NVARCHAR(255) PRIMARY KEY  );  GO    INSERT dbo.NaughtyUserNames VALUES('admin');  GO

  

After 트리거를 생성한다. 

CREATE TRIGGER dbo.trUserNames_After  ON dbo.UserNames_After  AFTER INSERT  AS  BEGIN    IF EXISTS     (     SELECT 1 FROM inserted AS i   WHERE EXISTS   (     SELECT 1 FROM dbo.NaughtyUserNames     WHERE Name = i.Name   )    )    BEGIN      RAISERROR('You used a admin name!', 11, 1);      ROLLBACK TRANSACTION;    END  END  GO

 

데이터를 입력 한다. 사용자가 이름을 입력하고 실행하면 예외가 발생하고 트랜잭션이 롤백 한다. 

INSERT dbo.UserNames_After(Name) SELECT 'admin';

 

메시지 50000, 수준 11, 상태 1, 프로시저 trUserNames_After, 줄 16  You used a admin name!  메시지 3609, 수준 16, 상태 1, 줄 1  트리거가 발생하여 트랜잭션이 종료되었습니다. 일괄 처리가 중단되었습니다.

 

 

 

 

INSTEAD OF 트리거를 생성한다.  

CREATE TRIGGER dbo.trUserNames_InsteadOf  ON dbo.UserNames_InsteadOf  INSTEAD OF INSERT  AS  BEGIN    IF NOT EXISTS     (      SELECT 1 FROM inserted AS i   WHERE EXISTS   (     SELECT 1 FROM dbo.NaughtyUserNames     WHERE Name = i.Name   )    )    BEGIN      INSERT dbo.UserNames_InsteadOf(Name) SELECT Name FROM inserted;    END    ELSE    BEGIN      RAISERROR('You used a admin name!', 11, 1);    END  END  GO

 

다음 스크립트는 10%의 비율로 admin 이름이 사용된 경우 실패를 반환하지만 테이블에 기록을 하지 않아 롤백을 수행 하지 않는다. 

TRUNCATE TABLE dbo.UserNames_After;  TRUNCATE TABLE dbo.UserNames_InsteadOf;    CHECKPOINT;    SELECT COUNT(*) FROM sys.fn_dblog(NULL, NULL);    SELECT     CurrentSizeMB = size/128.0,      FreeSpaceMB = (size - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT))/128.0  FROM sys.database_files  WHERE name LIKE '%[_]log';     DECLARE @n NVARCHAR(255), @u UNIQUEIDENTIFIER;    DECLARE c CURSOR LOCAL FAST_FORWARD  FOR     SELECT TOP (900) name = o.name + '/' + c.name, u = NEWID()      FROM sys.all_objects AS o      INNER JOIN sys.all_columns AS c      ON o.[object_id] = c.[object_id]    UNION ALL     SELECT TOP (100) name = 'admin', u = NEWID()      FROM sys.all_objects    ORDER BY u;    OPEN c;    FETCH c INTO @n, @u;    SELECT SYSDATETIME();    WHILE @@FETCH_STATUS = 0  BEGIN    BEGIN TRY      INSERT dbo.UserNames_InsteadOf(name) SELECT @n;      --INSERT dbo.UserNames_After(name) SELECT @n;    END TRY    BEGIN CATCH       PRINT 'Failed';    END CATCH    FETCH c INTO @n, @u;  END    SELECT SYSDATETIME();    CLOSE c; DEALLOCATE c;    SELECT COUNT(*) FROM sys.fn_dblog(NULL, NULL);    SELECT     CurrentSizeMB = size/128.0,      FreeSpaceMB = (size - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT))/128.0  FROM sys.database_files  WHERE name LIKE '%[_]log';

  

다음 표는 위의 스크립트를 사용하여 측정한 결과이다. INSTEAD OF 트리거를 사용하여 미리 비즈니스 검사를 수행하여 롤백의 시간을 줄여 빠른 성능을 나타내었다. 

 

출처 : https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=sqlmvp&logNo=140203114186 

AFTER 트리를 INSTEAD OF 트리거로 변경 후 효율성 향상