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

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

SQL Server Trigger

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

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
1730 SQL Server에서 Trigger 활성 / 비활성 감시 jevida(강성욱) 2016.09.14 1458
1729 DDL Trigger를 이용한 데이터베이스 변경 사항 추적 jevida(강성욱) 2016.09.14 1227
1728 Trigger를 이용한 SQL Server 커넥션 풀링 확인 jevida(강성욱) 2016.09.14 1087
» SQL Server Trigger jevida(강성욱) 2016.09.14 930
1726 인덱스에 대한 SORT_IN_TEMPDB 옵션 jevida(강성욱) 2016.09.14 846
1725 인덱스 DDL 작업의 디스크 공간 요구 사항 jevida(강성욱) 2016.09.14 911
1724 XML nodes() 함수를 이용한 OPENXML 교체 jevida(강성욱) 2016.09.14 940
1723 XQuery를 사용한 XML 데이터 업데이트 jevida(강성욱) 2016.09.14 1855
1722 BCP XML 파일 형식 jevida(강성욱) 2016.09.14 1250
1721 SQL Server로 데이터 가져오기 jevida(강성욱) 2016.09.14 1222
1720 SQL Server Stored Procedure 암호화 jevida(강성욱) 2016.09.14 2649
1719 SQL Server 대칭키 vs 비대칭키 암호화 jevida(강성욱) 2016.09.14 1682
1718 SQL Server 마스터 키 관리 jevida(강성욱) 2016.09.14 1702
1717 대칭키를 사용하여 SQL Server 암호화(열 수준) 하기 jevida(강성욱) 2016.09.13 5583
1716 DMV를 사용하여 누락된 인덱스 확인 jevida(강성욱) 2016.09.13 1336
1715 DMV를 이용한 SQL Server 대기 상태 확인 jevida(강성욱) 2016.09.13 3654
1714 DMV를 이용한 SQL Server 성능 카운터 확인 jevida(강성욱) 2016.09.13 1745
1713 DMV를 이용한 SQL Server IO 성능 모니터 스냅샷 만들기 jevida(강성욱) 2016.09.13 1239
1712 DMV를 활용한 SQL Server 모니터링 jevida(강성욱) 2016.09.13 1254
1711 세션에 따른 캐시된 쿼리 플랜 설정 확인 jevida(강성욱) 2016.09.13 862





XE Login