데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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를 지정할 수 없다.

 

이번 포스트는 트리거를 사용 할 때 AFTER 트리거를 INSTEAD OF 트리거로 변경하여 트리거의 효율성을 높인 사례이다. INSTEADOF 트리거는 작업을 수행하기 전에 비즈니스 규칙을 확인 할 수 있어 로그 롤백을 할 필요가 없어 효율적이다. 특히 속도가 느린 디스크에서는 더욱 효율 적이다.

 

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

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 트리거를 사용하여 미리 비즈니스 검사를 수행하여 롤백의 시간을 줄여 빠른 성능을 나타내었다.

 

 

위의 결과를 토대로 무조건 INSTEAD OF 트리거가 빠르다거나 AFTER 트리거가 느리다고 판단하면 위험하다. 비즈니스에 맞게 트리거를 잘 활용하는 것이 중요하다. 위 사례를 바탕으로 다양한 트리거의 활용을 생각해보면 좋을 듯 하다.

 

[참고자료]

 

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1910 64비트 버전의 SQL Server 버퍼 풀 메모리 페이지 수 줄이는 방법 jevida(강성욱) 2016.10.12 2432
1909 누락된 공유 잠금 (Missing Shared Locks) jevida(강성욱) 2016.10.12 1560
1908 Ad Hoc Distributed Queries 옵션 jevida(강성욱) 2016.10.12 3045
1907 높은 MAXDOP은 쿼리를 느리게 만들 수 있는가? jevida(강성욱) 2016.10.12 1305
1906 변경된 테이블 이름 복구하기 jevida(강성욱) 2016.10.12 1290
1905 Sys,dm_exec_connections jevida(강성욱) 2016.10.12 1908
1904 SQL Server 시작 매개 변수 설정 jevida(강성욱) 2016.10.12 2479
» AFTER 트리거를 INSTEAD OF 트리거로 변경 후 효율성 향상 jevida(강성욱) 2016.10.12 1782
1902 쿼리 대기 옵션 jevida(강성욱) 2016.10.12 1564
1901 Index create memory 설정 jevida(강성욱) 2016.10.12 1230
1900 Min memory per query 옵션 jevida(강성욱) 2016.10.12 1677
1899 SQL Server 에디션 다운그레이드와 제한된 기능 확인 jevida(강성욱) 2016.10.12 2121
1898 Downgrade from SQL Server Ent to Std Edition jevida(강성욱) 2016.10.12 1336
1897 트랜잭션 로그 여유 공간 모니터링 jevida(강성욱) 2016.10.12 1342
1896 SQL Server가 서비스 격리를 처리하는 방법 jevida(강성욱) 2016.10.12 1144
1895 쿼리 매개변수화 확인하기 jevida(강성욱) 2016.10.12 1428
1894 SQL Server에서 Drop 및 Delete 사용자 찾기 jevida(강성욱) 2016.10.12 1628
1893 SQL Server 트랜잭션 로그 읽기 jevida(강성욱) 2016.10.12 2067
1892 MaxBCPThreads에 따른 BCP 병렬출력 jevida(강성욱) 2016.10.11 1088
1891 확장이벤트와 dm_os_wait_stats 대기유형 매핑 jevida(강성욱) 2016.10.11 1883





XE Login