SQL Server Trigger

jevida(강성욱) 2016.09.14 06:52 Views : 1004

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/

 

 

 

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

 



강성욱 / jevida@naver.com

Microsoft SQL Server MVP

Blog : http://sqlmvp.kr

Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 38455
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 20748
1734 SQL Server Plan Guide 생성 및 사용 jevida(강성욱) 2016.09.14 1557
1733 SQL Server 그래픽 실행 계획 노드 정보 jevida(강성욱) 2016.09.14 1138
1732 프로파일러를 이용한 실행계획 캡처하기 jevida(강성욱) 2016.09.14 1010
1731 SQL Server 그래픽 실행 계획 및 텍스트 실행 계획 jevida(강성욱) 2016.09.14 3269
1730 SQL Server에서 Trigger 활성 / 비활성 감시 jevida(강성욱) 2016.09.14 1696
1729 DDL Trigger를 이용한 데이터베이스 변경 사항 추적 jevida(강성욱) 2016.09.14 1442
1728 Trigger를 이용한 SQL Server 커넥션 풀링 확인 jevida(강성욱) 2016.09.14 1140
» SQL Server Trigger jevida(강성욱) 2016.09.14 1004
1726 인덱스에 대한 SORT_IN_TEMPDB 옵션 jevida(강성욱) 2016.09.14 915
1725 인덱스 DDL 작업의 디스크 공간 요구 사항 jevida(강성욱) 2016.09.14 975
1724 XML nodes() 함수를 이용한 OPENXML 교체 jevida(강성욱) 2016.09.14 1129
1723 XQuery를 사용한 XML 데이터 업데이트 jevida(강성욱) 2016.09.14 2016
1722 BCP XML 파일 형식 jevida(강성욱) 2016.09.14 1330
1721 SQL Server로 데이터 가져오기 jevida(강성욱) 2016.09.14 1366
1720 SQL Server Stored Procedure 암호화 jevida(강성욱) 2016.09.14 2824
1719 SQL Server 대칭키 vs 비대칭키 암호화 jevida(강성욱) 2016.09.14 1785
1718 SQL Server 마스터 키 관리 jevida(강성욱) 2016.09.14 2070
1717 대칭키를 사용하여 SQL Server 암호화(열 수준) 하기 jevida(강성욱) 2016.09.13 5938
1716 DMV를 사용하여 누락된 인덱스 확인 jevida(강성욱) 2016.09.13 1426
1715 DMV를 이용한 SQL Server 대기 상태 확인 jevida(강성욱) 2016.09.13 4485





XE Login