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

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

SQL Server Trigger

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

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
1731 SQL Server 그래픽 실행 계획 및 텍스트 실행 계획 jevida(강성욱) 2016.09.14 3126
1730 SQL Server에서 Trigger 활성 / 비활성 감시 jevida(강성욱) 2016.09.14 1469
1729 DDL Trigger를 이용한 데이터베이스 변경 사항 추적 jevida(강성욱) 2016.09.14 1230
1728 Trigger를 이용한 SQL Server 커넥션 풀링 확인 jevida(강성욱) 2016.09.14 1088
» SQL Server Trigger jevida(강성욱) 2016.09.14 938
1726 인덱스에 대한 SORT_IN_TEMPDB 옵션 jevida(강성욱) 2016.09.14 846
1725 인덱스 DDL 작업의 디스크 공간 요구 사항 jevida(강성욱) 2016.09.14 912
1724 XML nodes() 함수를 이용한 OPENXML 교체 jevida(강성욱) 2016.09.14 945
1723 XQuery를 사용한 XML 데이터 업데이트 jevida(강성욱) 2016.09.14 1883
1722 BCP XML 파일 형식 jevida(강성욱) 2016.09.14 1255
1721 SQL Server로 데이터 가져오기 jevida(강성욱) 2016.09.14 1223
1720 SQL Server Stored Procedure 암호화 jevida(강성욱) 2016.09.14 2689
1719 SQL Server 대칭키 vs 비대칭키 암호화 jevida(강성욱) 2016.09.14 1687
1718 SQL Server 마스터 키 관리 jevida(강성욱) 2016.09.14 1750
1717 대칭키를 사용하여 SQL Server 암호화(열 수준) 하기 jevida(강성욱) 2016.09.13 5673
1716 DMV를 사용하여 누락된 인덱스 확인 jevida(강성욱) 2016.09.13 1343
1715 DMV를 이용한 SQL Server 대기 상태 확인 jevida(강성욱) 2016.09.13 3807
1714 DMV를 이용한 SQL Server 성능 카운터 확인 jevida(강성욱) 2016.09.13 1787
1713 DMV를 이용한 SQL Server IO 성능 모니터 스냅샷 만들기 jevida(강성욱) 2016.09.13 1244
1712 DMV를 활용한 SQL Server 모니터링 jevida(강성욱) 2016.09.13 1261





XE Login