SQL Server Trigger

SQL Server Trigger

 

• Version : SQL Server 2000, 2005, 2008, 2008R2, 2012

 

데이터베이스 서버 활동에서 감사가 필요할 때(시스템 변경, 데이터 조작 등)변경 사항 등을 수집 할 수 있다.

SQL Server 2000에서는 로그인 감사 및 DML(데이터 조작 언어)을 사용하여 데이터 변경 사항을 캡처 하였다. SQL Server 2005 부터는 DDL(데이터 정의 언어) 트리거가 도입 되었다.

 

[DML 트리거]

CREATE TRIGGER [ schema_name . ]trigger_name ON { table | view } [ WITH <dml_trigger_option> [ ,...n ] ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }   <dml_trigger_option> ::= [ ENCRYPTION ] [ EXECUTE AS Clause ]   <method_specifier> ::= assembly_name.class_name.method_name

 

[DDL 트리거]

CREATE TRIGGER trigger_name ON { ALL SERVER | DATABASE } [ WITH <ddl_trigger_option> [ ,...n ] ] { FOR | AFTER } { event_type | event_group } [ ,...n ] AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] }   <ddl_trigger_option> ::= [ ENCRYPTION ] [ EXECUTE AS Clause ]   <method_specifier> ::= assembly_name.class_name.method_name

 

[Logon 트리거]

CREATE TRIGGER trigger_name ON ALL SERVER [ WITH <logon_trigger_option> [ ,...n ] ] { FOR | AFTER } LOGON AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] }   <logon_trigger_option> ::= [ ENCRYPTION ] [ EXECUTE AS Clause ]   <method_specifier> ::= assembly_name.class_name.method_name

 

 

[DDL 트리거용 이벤트 그룹]

 

 

[Logon 트리거 실습]

SQL Server 로그인시 트리거에 의해 로그온 기록을 남겨보도록 한다.

실습용 테이블을 준비 한다.

CREATE TABLE LogOnTrigger ( LogonName NVARCHAR(100), LogonDate DATETIME )

 

Logon 트리거를 생성 한다.

CREATE TRIGGER LogonTrigger ON ALL SERVER FOR LOGON AS BEGIN     INSERT INTO SW_TEST.dbo.LogOnTrigger VALUES (ORIGINAL_LOGIN(), GETDATE()) END

 

 

 

새로운 SSMS를 실행하여 데이터베이스에 접속 한다.

 

 

트리거 기록 테이블을 조회해 보면 로그인시의 계정 이름과 로그인 시간을 확인 할 수 있다.

 

 

 

트리거가 설정되어 있는 테이블이 삭제 된 경우 로그인이 실패 한다.

이 때에는 관리자 전용 연결 모드(DAC)로 접속하여 트리거 삭제 하거나 해당 테이블을 생성하여 문제를 해결 한다.

 

DAC 참고 : http://sqlmvp.kr/140173214035

 

로그인 트리거의 응용은 다음 자료를 참고 하자.

김민석님의 Logon 트리거 활용 : http://cafe.naver.com/sqlmvp/513

 

[참고 링크]

http://www.mssqltips.com/sqlservertip/1006/auditing-ddl-create-alter-drop-commands-in-sql-server-2005/

http://www.mssqltips.com/sqlservertip/1631/connecting-to-sql-server-with-a-bad-logon-trigger/

 

 

 

트리거를 활용할 때 서버 또는 데이터베이스 수준에서 이벤트를 수집할 것인지 결정해야 한다. 수집한 데이터를 기반으로 시스템을 모니터링 할 수 있다.

 

2013-02-13 / 강성욱 / http://sqlmvp.kr