CREATE TRIGGER(Transact-SQL)

 

이 항목은 다음에 적용됩니다.예SQL Server(2008부터 시작)예Azure SQL 데이터베이스아니요Azure SQL 데이터 웨어하우스아니요병렬 데이터 웨어하우스

DML, DDL 또는 LOGON 트리거를 만듭니다. 트리거는 데이터베이스 서버에서 이벤트가 발생하면 자동으로 실행되는 특수한 종류의 저장 프로시저입니다. DML 트리거는 DML(데이터 조작 언어) 이벤트를 통해 데이터를 수정하려는 경우에 실행됩니다. DML 이벤트는 테이블이나 뷰에 대한 INSERT, UPDATE 또는 DELETE 문입니다. 테이블 행이 영향을 받는지 여부에 관계없이 유효한 이벤트가 발생할 때 이러한 트리거가 발생합니다. 자세한 내용은 DML Triggers을 참조하세요.

DDL 트리거는 다양한 DDL(데이터 정의 언어) 이벤트에 대한 응답으로 실행됩니다. 이러한 이벤트는 주로 Transact-SQL CREATE, ALTER 및 DROP 문, DDL과 같은 작업을 수행하는 특정 시스템 저장 프로시저에 해당합니다. LOGON 트리거는 사용자 세션이 설정될 때 발생하는 LOGON 이벤트에 대한 응답으로 실행됩니다. 직접 트리거를 만들 수 있습니다 Transact-SQL 문 내에 만들어진 어셈블리의 메서드로 Microsoft .NET Framework 공용 언어 런타임 (CLR)의 인스턴스에 업로드 하 고 SQL Server. SQL Server를 사용하면 특정 문에 대한 여러 트리거를 만들 수 있습니다.

System_CAPS_ICON_important.jpg 중요


사용 권한 수준을 높이고 트리거를 실행하더라도 트리거 내의 악성 코드가 실행될 수 있습니다. 이 위협을 완화 하는 방법에 대 한 자세한 내용은 참조 하십시오. 트리거 보안 관리합니다.

System_CAPS_ICON_note.jpg 참고


SQL Server에.NET Framework CLR의 통합은이 항목에 설명 되어 있습니다. CLR 통합은 Azure SQL 데이터베이스에 적용 되지 않습니다.

Topic link icon Transact-SQL 구문 표기 규칙

  
-- SQL Server Syntax  
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)  
  
CREATE [ OR ALTER ] 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  
  

-- SQL Server Syntax  
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table (DML Trigger on memory-optimized tables)  
  
CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
ON { table }   
[ WITH <dml_trigger_option> [ ,...n ] ]  
{ FOR | AFTER }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
AS { sql_statement  [ ; ] [ ,...n ] }  
  
<dml_trigger_option> ::=  
    [ NATIVE_COMPILATION ]  
    [ SCHEMABINDING ]  
    [ EXECUTE AS Clause ]  
  

-- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE or UPDATE statement (DDL Trigger)  
  
CREATE [ OR ALTER ] 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 ]  
  

-- Trigger on a LOGON event (Logon Trigger)  
  
CREATE [ OR ALTER ] 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 ]  
  

-- Windows Azure SQL Database Syntax   
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)  
  
CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
ON { table | view }   
 [ WITH <dml_trigger_option> [ ,...n ] ]   
{ FOR | AFTER | INSTEAD OF }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
  AS { sql_statement  [ ; ] [ ,...n ] [ ; ] > }  
  
<dml_trigger_option> ::=   
        [ EXECUTE AS Clause ]  
  

-- Windows Azure SQL Database Syntax  
-- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)   
  
CREATE [ OR ALTER ] TRIGGER trigger_name   
ON { DATABASE }   
 [ WITH <ddl_trigger_option> [ ,...n ] ]   
{ FOR | AFTER } { event_type | event_group } [ ,...n ]   
AS { sql_statement  [ ; ] [ ,...n ]  [ ; ] }  
  
<ddl_trigger_option> ::=   
    [ EXECUTE AS Clause ]  

또는 변경

적용 대상: Azure SQL 데이터베이스, SQL Server (부터는 SQL Server 2016 s p&1;).

조건에 따라 이미 있는 경우에 트리거를 변경 합니다.

schema_name
DML 트리거가 속한 스키마의 이름입니다. DML 트리거는 트리거가 생성된 테이블 또는 뷰의 스키마로 한정됩니다. schema_name DDL 또는 logon 트리거에 대해 지정할 수 없습니다.

trigger_name
트리거의 이름입니다. A trigger_name 에 대 한 규칙을 준수 해야 식별자점을 제외 하 고 trigger_name #로 시작할 수 없습니다 또는 # #.

table | view
DML 트리거가 실행되는 테이블 또는 뷰이며 트리거 테이블 또는 트리거 뷰라고도 합니다. 테이블 또는 뷰의 정규화된 이름을 지정하는 것은 옵션입니다. 뷰는 INSTEAD OF 트리거에서만 참조될 수 있습니다. 로컬 또는 전역 임시 테이블에는 DML 트리거를 정의할 수 없습니다.

DATABASE
현재 데이터베이스에 DDL 트리거의 해당 범위를 적용합니다. 를 지정 하는 경우 트리거가 때마다 event_type 또는 event_group 현재 데이터베이스에서 발생 합니다.

ALL SERVER

적용 대상: SQL Server 2008 부터 SQL Server 2016까지

현재 서버에 DDL 또는 LOGON 트리거의 범위를 적용합니다. 를 지정 하는 경우 트리거가 때마다 event_type 또는 event_group 현재 서버에서 발생 합니다.

WITH ENCRYPTION

적용 대상: SQL Server 2008 부터 SQL Server 2016까지

CREATE TRIGGER 문의 텍스트를 난독 처리합니다. WITH ENCRYPTION을 사용하면 트리거가 SQL Server 복제의 일부로 게시되지 않도록 방지할 수 있습니다. CLR 트리거에 대해서는 WITH ENCRYPTION을 지정할 수 없습니다.

EXECUTE AS
트리거가 실행되는 보안 컨텍스트를 지정합니다. 이를 통해 트리거에서 참조되는 모든 데이터베이스 개체에 대한 사용 권한 유효성을 검사하기 위해 SQL Server 인스턴스가 사용하는 사용자 계정을 제어할 수 있습니다.

이 옵션은 메모리 액세스에 최적화 된 테이블의 트리거에 필요 합니다.

자세한 내용은 참조EXECUTE AS Clause (TRANSACT-SQL)합니다.

NATIVE_COMPILATION
트리거는 기본적으로 컴파일 되었음을 나타냅니다.

이 옵션은 메모리 액세스에 최적화 된 테이블의 트리거에 필요 합니다.

SCHEMABINDING
트리거에 의해 참조 되는 테이블 삭제 하거나 변경할 수 없는 확인 합니다.

이 옵션에 메모리 액세스에 최적화 된 테이블의 트리거 실행 되어야 하 고 기존 테이블의 트리거에 대 한 지원 되지 않습니다.

FOR | AFTER
AFTER는 DML 트리거를 시작하는 SQL 문에서 지정한 모든 작업이 성공적으로 실행되었을 때만 트리거가 실행되도록 지정합니다. 모든 참조 연계 동작 및 제약 조건 검사도 이 트리거가 실행되기 전에 성공해야 합니다.

지정된 키워드가 FOR뿐인 경우에는 AFTER가 기본값입니다.

뷰에 대해서는 AFTER 트리거를 정의할 수 없습니다.

INSTEAD OF
DML 트리거가 실행 되도록 지정 대신 따라서 SQL 문의 트리거, 트리거 문의 동작을 재정의 합니다. DDL 또는 LOGON 트리거에 대해서는 INSTEAD OF를 지정할 수 없습니다.

테이블이나 뷰에 대해 INSERT, UPDATE 또는 DELETE 문당 INSTEAD OF 트리거를 하나만 정의할 수 있습니다. 그러나 고유한 INSTEAD OF 트리거가 있는 각 뷰에 대해 뷰를 정의할 수 있습니다.

WITH CHECK OPTION을 사용하는 업데이트 가능한 뷰에는 INSTEAD OF 트리거를 사용할 수 없습니다. WITH CHECK OPTION이 지정된 업데이트할 수 있는 뷰에 INSTEAD OF 트리거를 추가하면 SQL Server에서 오류가 발생하기 때문에, INSTEAD OF 트리거를 정의하기 전에 ALTER VIEW를 사용하여 해당 옵션을 제거해야 합니다.

{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
이 테이블이나 뷰에 수행될 경우 DML 트리거를 활성화하는 데이터 수정 문을 지정합니다. 적어도 하나의 옵션을 지정해야 합니다. 트리거 정의에서는 이러한 옵션을 순서에 관계없이 어떤 방법으로도 조합할 수 있습니다.

INSTEAD OF 트리거의 경우 ON DELETE 연계 동작을 지정하는 참조 관계가 있는 테이블에 대해서는 DELETE 옵션을 사용할 수 없습니다. 마찬가지로 연계 동작인 ON UPDATE를 지정하는 참조 관계가 있는 테이블에 대해서도 UPDATE 옵션이 허용되지 않습니다.

WITH APPEND

적용 대상: SQL Server 2008 부터 SQL Server 2008 R2까지

기존 유형의 추가 트리거를 반드시 추가하도록 지정합니다. INSTEAD OF 트리거가 사용되거나 AFTER 트리거가 명시적으로 지정된 경우에는 WITH APPEND를 사용할 수 없습니다. WITH APPEND는 FOR가 INSTEAD OF 또는 AFTER 없이 지정되어 있는 경우에만 이전 버전과의 호환성을 위해 사용할 수 있습니다. EXTERNAL NAME이 지정된 경우(트리거가 CLR 트리거인 경우)에는 WITH APPEND를 지정할 수 없습니다.

event_type
실행된 후에 DDL 트리거가 실행되도록 하는 Transact-SQL 언어 이벤트의 이름입니다. DDL 트리거에 유효한 이벤트에 나열 됩니다 DDL 이벤트합니다.

event_group
Transact-SQL 언어 이벤트의 미리 정의된 그룹 이름입니다. 실행 된 후에 DDL 트리거가 실행 Transact-SQL 에 속하는 언어 이벤트 event_group합니다. DDL 트리거에 유효한 이벤트 그룹에 나열 됩니다 DDL 이벤트 그룹합니다.

CREATE TRIGGER 실행이 완료 된 후 event_group 도 매크로로 하 여 역할 추가 이벤트 유형을 sys.trigger_events 카탈로그 뷰에 있습니다.

NOT FOR REPLICATION

적용 대상: SQL Server 2008 부터 SQL Server 2016까지

복제 에이전트가 트리거와 연관된 테이블을 수정할 때 트리거를 실행할 수 없다는 것을 나타냅니다.

sql_statement
트리거 조건 및 동작입니다. 트리거 조건은 시도된 DML, DDL 또는 LOGON 이벤트가 트리거 동작을 수행하게 하는지 여부를 결정하는 추가 조건을 지정합니다.

Transact-SQL 문에 지정된 트리거 동작은 해당 작업이 시도될 때 적용됩니다.

일부 예외가 있지만 트리거는 수와 종류에 관계없이 Transact-SQL 문을 포함할 수 있습니다. 자세한 내용은 설명 부분을 참조하세요. 트리거는 데이터 수정 또는 정의 문을 기반으로 하여 데이터를 확인하거나 변경하도록 설계되었습니다. 따라서 데이터를 사용자에게 반환해서는 안 됩니다. Transact-SQL 문은 트리거 내에서 자주 포함 합니다. 흐름 제어 언어합니다.

DML 트리거는 deleted 및 inserted 논리(개념) 테이블을 사용합니다. 이러한 테이블은 구조적으로 트리거가 정의되어 있는 테이블(사용자 동작이 수행되는 테이블)과 유사합니다. deleted 및 inserted 테이블에는 사용자 동작으로 변경될 수 있는 행의 이전 값과 새 값이 유지됩니다. 예를 들어, deleted 테이블의 모든 값을 검색하려면 다음을 사용합니다.

SELECT * FROM deleted;  

자세한 내용은 참조 inserted 및 deleted 테이블을 사용 하 여합니다.

DDL 및 logon 트리거를 사용 하 여 트리거 이벤트에 대 한 정보를 캡처하는 EVENTDATA (TRANSACT-SQL) 함수입니다. 자세한 내용은 참조 EVENTDATA 함수를 사용 하 여합니다.

SQL Server업데이트에 대 한 허용 텍스트, ntext, 또는 이미지 테이블이 나 뷰에 INSTEAD OF 통해 트리거할 합니다.

System_CAPS_ICON_important.jpg 중요


ntext, 텍스트, 및 이미지 데이터 형식을의 이후 버전에서 제거 됩니다 Microsoft SQL Server합니다. 향후 개발 작업에서는 이 데이터 형식을 사용하지 않도록 하고 현재 이 데이터 형식을 사용하는 응용 프로그램은 수정하세요. 대신 nvarchar(max), varchar(max)varbinary(max)를 사용합니다. AFTER 및 INSTEAD OF 트리거는 지원 varchar (max), nvarchar (max), 및 varbinary (max) inserted 및 deleted 테이블의 데이터입니다.

메모리 액세스에 최적화 된 테이블의 트리거에 대 한 유일한 sql_statement ATOMIC 블록은 최상위 수준에서 사용할 수 있습니다. ATOMIC 블록 내에서 허용 하는 T-SQL 네이티브 프로시저 내에서 허용 하는 T-SQL으로 제한 됩니다.

< method_specifier >

적용 대상: SQL Server 2008 부터 SQL Server 2016까지

CLR 트리거의 경우 트리거와 바인딩할 어셈블리의 메서드를 지정합니다. 이 메서드는 인수가 없어야 하며 void를 반환해야 합니다. 눈여겨 보십시오 은 유효한 SQL Server 식별자 하며 어셈블리 표시 유형이 있는 어셈블리에 클래스로 존재 해야 합니다. 클래스가 마침표 '.'를 사용하여 네임스페이스 부분을 구분하는 네임스페이스로 한정된 이름을 가질 경우 클래스 이름은 [ ] 또는 " " 구분 기호를 사용하여 구분되어야 합니다. 클래스는 중첩 클래스일 수 없습니다.

System_CAPS_ICON_note.jpg 참고


기본적으로 SQL Server의 CLR 코드 실행 기능은 해제됩니다. 만들기, 수정 및 관리 코드 모듈을 참조 하는 데이터베이스 개체를 삭제할 수 있지만 이러한 참조의 인스턴스에서 실행 되지 것입니다 SQL Server 하지 않는 한는 옵션을 사용 하는 clr 사용 하 여 sp_configure합니다.

DML 트리거는 비즈니스 규칙 및 데이터 무결성을 적용하는 데 자주 사용됩니다. SQL Server는 ALTER TABLE 및 CREATE TABLE 문을 통해 DRI(선언적 참조 무결성)를 제공하지만 DRI는 데이터베이스 간 참조 무결성은 제공하지 않습니다. 참조 무결성은 테이블의 기본 키와 외래 키 간의 관계에 대한 규칙을 말합니다. 참조 무결성을 강제 적용하려면 ALTER TABLE 및 CREATE TABLE에서 PRIMARY KEY 및 FOREIGN KEY 제약 조건을 사용하세요. 제약 조건이 트리거 테이블에 있는 경우에는 INSTEAD OF 트리거가 실행된 후와 AFTER 트리거가 실행되기 전에 제약 조건이 확인됩니다. 제약 조건을 위반하면 INSTEAD OF 트리거 동작이 롤백되고 AFTER 트리거가 실행되지 않습니다.

sp_settriggerorder를 사용하여 테이블에서 실행할 처음 및 마지막 AFTER 트리거를 지정할 수 있습니다. 테이블에서 각각의 INSERT, UPDATE 및 DELETE 작업에 대해 처음 및 마지막 AFTER 트리거를 각각 하나만 정의할 수 있습니다. 동일한 테이블에 다른 AFTER 트리거가 있는 경우 임의로 실행됩니다.

ALTER TRIGGER 문에서 첫 번째 트리거나 마지막 트리거를 변경하면 수정된 트리거에 설정된 첫 번째 또는 마지막 특성은 삭제되며 sp_settriggerorder를 사용하여 순서 값을 다시 설정해야 합니다.

AFTER 트리거는 트리거를 시작하는 SQL 문이 성공적으로 실행된 후에만 실행됩니다. 또한 업데이트 또는 삭제된 개체와 관련된 모든 참조 연계 동작과 제약 조건 확인이 성공적으로 수행되어야 합니다. AFTER 트리거는 동일한 테이블에서 INSTEAD OF 트리거를 재귀적으로 실행합니다.

테이블에 정의된 INSTEAD OF 트리거가 테이블에 대해 보통 INSTEAD OF 트리거를 다시 시작하는 문을 실행하면 트리거가 재귀적으로 호출되지 않습니다. 그 대신 테이블에 INSTEAD OF 트리거가 없는 것처럼 처리되어 제약 조건 작업 및 AFTER 트리거 실행 체인을 시작합니다. 예를 들어, 트리거가 테이블에 대해 INSTEAD OF INSERT 트리거로 정의되고 트리거가 동일한 테이블에서 INSERT 문을 실행하면 INSTEAD OF 트리거에서 실행하는 INSERT 문이 트리거를 다시 호출하지 않습니다. 트리거가 실행하는 INSERT 문은 제약 조건 동작을 수행하고 테이블에 대해 정의된 AFTER INSERT 트리거를 실행하는 프로세스를 시작합니다.

뷰에 정의된 INSTEAD OF 트리거가 뷰에 대해 보통 INSTEAD OF 트리거를 다시 시작하는 문을 실행하면 트리거가 재귀적으로 호출되지 않습니다. 그 대신 문이 뷰의 원본인 기준 테이블에 대한 수정으로 확인됩니다. 이런 경우 뷰 정의는 업데이트할 수 있는 뷰에 대한 모든 제한을 충족해야 합니다. 업데이트할 수 있는 뷰 정의 참조 하십시오. 수정 데이터 뷰를 통해합니다.

예를 들어, 트리거가 뷰에 대해 INSTEAD OF UPDATE 트리거로 정의되고 트리거가 같은 뷰를 참조하는 UPDATE 문을 실행하면 INSTEAD OF 트리거가 실행하는 UPDATE 문은 트리거를 다시 호출하지 않습니다. 트리거가 실행하는 UPDATE는 뷰에 대해 뷰에 INSTEAD OF 트리거가 없는 것처럼 처리됩니다. UPDATE에 의해 변경된 열은 단일 기준 테이블로 확인되어야 합니다. 원본으로 사용하는 기준 테이블을 수정할 때마다 제약 조건 적용 및 테이블에 대해 정의된 AFTER 트리거 시작 체인을 시작합니다.

특정 열에 대한 UPDATE 또는 INSERT 동작 테스트

특정 열에 대한 UPDATE 또는 INSERT 수정 사항을 기반으로 특정 동작을 수행하도록 Transact-SQL 트리거를 디자인할 수 있습니다. 사용 하 여 update () 또는 COLUMNS_UPDATED 이 목적을 위해 트리거 본문에 있습니다. UPDATE()는 열 하나에 대한 UPDATE 또는 INSERT 작업을 테스트하고, COLUMNS_UPDATED는 여러 열에 수행되는 UPDATE 또는 INSERT 동작을 테스트한 다음 삽입되거나 업데이트된 열을 나타내는 비트 패턴을 반환합니다.

트리거 제한

CREATE TRIGGER는 일괄 처리의 첫 번째 문이어야 하며 한 테이블에만 적용될 수 있습니다.

트리거는 현재 데이터베이스에서만 만들어집니다. 그러나 트리거는 현재 데이터베이스 밖의 개체도 참조할 수 있습니다.

트리거를 한정하기 위해 트리거 스키마 이름을 지정한 경우에는 같은 방법으로 테이블 이름을 한정하세요.

같은 CREATE TRIGGER 문에서 둘 이상의 사용자 작업(예: INSERT 및 UPDATE)에 대해 같은 트리거 동작을 정의할 수 있습니다.

연계 DELETE/UPDATE 동작에 대해 외래 키가 정의된 테이블에 대해서는 INSTEAD OF DELETE/UPDATE 트리거를 정의할 수 없습니다.

트리거 내부에서 SET 문을 지정할 수 있습니다. 선택된 SET 옵션은 트리거 실행 중에만 적용되며 실행이 끝나면 이전 설정으로 돌아갑니다.

트리거가 실행되면 저장 프로시저와 마찬가지로 호출하는 응용 프로그램에 결과가 반환됩니다. 트리거 실행으로 인해 응용 프로그램에 결과가 반환되는 것을 방지하려면 결과를 반환하는 SELECT 문이나 트리거에서 변수 할당을 수행하는 문을 포함하지 마세요. 사용자에게 결과를 반환하는 SELECT 문이나 변수 할당을 수행하는 문을 포함하는 트리거는 특수하게 처리해야 합니다. 이렇게 반환된 결과는 트리거 테이블을 수정할 수 있도록 허용된 모든 응용 프로그램에 기록되어야 합니다. 트리거에서 변수를 할당해야 하는 경우에는 트리거 시작 부분에 SET NOCOUNT 문을 사용하여 모든 결과 집합이 반환되지 않게 하세요.

TRUNCATE TABLE 문은 사실상 DELETE 문과 같지만 이 작업은 개별 행 삭제를 로그하지 않으므로 트리거를 실행하지 않습니다. 하지만 TRUNCATE TABLE 문 실행 권한이 있는 사용자 외에는 이 문이 DELETE 트리거를 실수로 방해하는 것을 염려할 필요가 없습니다.

WRITETEXT 문은 기록 여부에 관계없이 트리거를 활성화하지 않습니다.

다음 Transact-SQL 문은 DML 트리거에서 사용할 수 없습니다.

ALTER DATABASECREATE DATABASEDROP DATABASE
RESTORE DATABASERESTORE LOGRECONFIGURE

또한 다음 Transact-SQL 문은 트리거를 실행하는 동작의 대상인 테이블이나 뷰에 사용될 경우 DML 트리거 본문에 사용할 수 없습니다.

CREATE INDEX(CREATE SPATIAL INDEX 및 CREATE XML INDEX 포함)ALTER INDEXDROP INDEX
DBCC DBREINDEXALTER PARTITION FUNCTIONDROP TABLE
다음 용도로 사용하는 ALTER TABLE

열 추가, 수정 또는 삭제

파티션 전환

PRIMARY KEY 또는 UNIQUE 제약 조건 추가 또는 삭제
System_CAPS_ICON_note.jpg 참고


SQL Server는 시스템 테이블에 대한 사용자 정의 트리거를 지원하지 않기 때문에 시스템 테이블에 대한 사용자 정의 트리거를 만들지 않는 것이 좋습니다.

DDL 트리거는 표준 트리거와 마찬가지로 이벤트에 대한 응답으로 저장 프로시저를 실행합니다. 하지만 표준 트리거와 달리 테이블이나 뷰의 UPDATE, INSERT 또는 DELETE 문에 대한 응답으로 실행되는 것이 아니라 기본적으로 DDL(데이터 정의 언어) 문에 대한 응답으로 실행됩니다. 이러한 DDL 문에는 CREATE, ALTER, DROP, GRANT, DENY, REVOKE 및 UPDATE STATISTICS 문이 포함됩니다. DDL과 같은 작업을 수행하는 특정 시스템 저장 프로시저에서 DDL 트리거가 발생할 수도 있습니다.

System_CAPS_ICON_important.jpg 중요


DDL 트리거를 테스트하여 시스템 저장 프로시저 실행에 대한 응답을 확인합니다. 예를 들어 CREATE TYPE 문과 sp_addtype 및 sp_rename 저장 프로시저는 모두 CREATE_TYPE 이벤트에서 생성되는 DDL 트리거를 발생시킵니다.

DDL 트리거에 대 한 자세한 내용은 참조 DDL 트리거합니다.

DDL 트리거는 로컬 또는 전역 임시 테이블과 저장 프로시저에 영향을 주는 이벤트에 대한 응답으로 실행되지 않습니다.

DDL 트리거는 DML 트리거와 달리 스키마로 범위가 한정되지 않습니다. DDL 트리거에 대한 메타데이터를 쿼리하는 데 OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY 및 OBJECTPROPERTYEX와 같은 함수는 사용할 수 없습니다. 대신 카탈로그 뷰를 사용하세요. 자세한 내용은 참조 DDL 트리거에 대 한 정보 가져오기합니다.

System_CAPS_ICON_note.jpg 참고


에 표시 된 서버 범위 DDL 트리거는 SQL Server Management Studio 의 개체 탐색기는 트리거 폴더입니다. 이 폴더는 서버 개체 폴더 아래에 있습니다. 에 표시 된 데이터베이스 범위 DDL 트리거는 데이터베이스 트리거 폴더입니다. 이 폴더는 해당 데이터베이스의 프로그래밍 기능 폴더 아래에 있습니다.

LOGON 트리거는 LOGON 이벤트에 대한 응답으로 저장 프로시저를 실행합니다. 이 이벤트는 SQL Server인스턴스에 사용자 세션이 설정된 경우 발생합니다. LOGON 트리거는 로그인의 인증 단계가 완료되었지만 사용자 세션이 실제로 설정되기 전에 발생합니다. 따라서 오류 메시지 및 PRINT 문의 메시지와 같이 일반적으로 사용자에게 전달되는 모든 트리거 내 발생 메시지는 SQL Server 오류 로그로 전달됩니다. 자세한 내용은 참조 Logon 트리거합니다.

인증에 실패할 경우 LOGON 트리거는 실행되지 않습니다.

분산 트랜잭션은 로그온 트리거에서 지원되지 않습니다. 분산 트랜잭션이 포함된 로그온 트리거가 발생되면 오류 3969가 반환됩니다.

로그온 트리거 비활성화

로그온 트리거를 사용하면 데이터베이스 엔진 sysadmin 고정 서버 역할의 멤버를 비롯한 모든 사용자에 대해 에 성공적으로 연결하지 못하도록 효과적으로 차단할 수 있습니다. 로그온 트리거가 연결을 차단 중인 경우 sysadmin 고정 서버 역할의 멤버는 관리자 전용 연결을 사용하거나 최소 구성 모드(-f)로 데이터베이스 엔진 을 시작하여 연결할 수 있습니다. 자세한 내용은 Database Engine Service Startup Options을(를) 참조하세요.

결과 반환

이후 버전의 SQL Server에서는 트리거에서 결과를 반환하는 기능이 제거됩니다. 결과 집합을 반환하는 트리거는 트리거가 작동하지 않는 응용 프로그램에 예기치 않은 동작을 유발할 수도 있습니다. 향후 개발 작업에서는 트리거에서 결과 집합을 반환하지 않도록 하고 현재 이 기능을 사용하는 응용 프로그램은 수정하세요. 트리거가 결과 집합을 반환 하지 않도록 하려면 설정의 disallow results from triggers 옵션 1입니다.

LOGON 트리거는 결과 집합 반환을 항상 허용하지 않으며 이 동작은 구성할 수 없습니다. LOGON 트리거가 결과 집합을 생성할 경우 트리거가 실행되지 않고 트리거를 실행한 로그인 시도가 거부됩니다.

다중 트리거

SQL Server에서는 각 DML, DDL 또는 LOGON 이벤트에 대해 다중 트리거를 만들 수 있습니다. 예를 들어, CREATE TRIGGER FOR UPDATE가 이미 UPDATE 트리거가 있는 테이블에 대해 실행된 경우에는 추가 업데이트 트리거가 만들어집니다. 이전 버전의 SQL Server에서는 각 테이블에서 DELETE, INSERT 또는 UPDATE 데이터 수정 이벤트 각각에 대해 트리거를 하나만 만들 수 있었습니다.

재귀 트리거

ALTER DATABASE를 사용하여 RECURSIVE_TRIGGERS 설정을 활성화한 경우 SQL Server에서 재귀적 트리거 호출을 사용할 수 있습니다.

재귀 트리거를 사용하면 다음 유형의 재귀 호출을 실행할 수 있습니다.

  • 간접 재귀

    간접 재귀에서는 응용 프로그램이 T1 테이블을 업데이트하면 TR1 트리거가 실행되어 T2 테이블을 업데이트합니다. 그런 다음 T2 트리거가 실행되어 T1 테이블을 업데이트합니다.

  • 직접 재귀

    직접 재귀에서는 응용 프로그램이 T1 테이블을 업데이트하면 TR1 트리거가 실행되어 T1 테이블을 업데이트합니다. T1 테이블이 업데이트되었으므로 TR1이 다시 실행되고 이런 식으로 계속됩니다.

간접 트리거 재귀와 직접 트리거 재귀를 모두 사용하는 다음 예에서는 두 업데이트 트리거 TR1 및 TR2가 T1 테이블에 정의되어 있다고 가정합니다. TR1 트리거는 T1 테이블을 재귀적으로 업데이트합니다. UPDATE 문은 각 TR1 및 TR2를 한 번 실행합니다. 또한 TR1을 실행하면 재귀적으로 TR1이 실행된 다음 TR2가 실행됩니다. 특정 트리거에 대한 inserted 및 deleted 테이블에는 트리거를 호출한 UPDATE 문에만 해당되는 행이 포함됩니다.

System_CAPS_ICON_note.jpg 참고


위 동작은 ALTER DATABASE를 사용하여 RECURSIVE_TRIGGERS 설정을 활성화한 경우에만 실행됩니다. 특정 이벤트에 정의된 다중 트리거의 실행 순서는 정해져 있지 않습니다. 각 트리거는 반드시 자기를 포함해야 합니다.

RECURSIVE_TRIGGERS 설정을 비활성화하면 직접 재귀만 금지됩니다. 간접 재귀도 비활성화하려면 sp_configure를 사용하여 nested triggers 서버 옵션을 0으로 설정합니다.

트리거 중 하나가 ROLLBACK TRANSACTION을 수행하는 경우에는 중첩 수준에 관계없이 더 이상 트리거가 실행되지 않습니다.

중첩 트리거

트리거는 최대 32 수준까지 중첩될 수 있습니다. 트리거가 있는 테이블을 다른 트리거가 변경하는 경우에는 두 번째 트리거가 활성화되고 이어서 세 번째 트리거가 호출되는 방식으로 진행됩니다. 체인 내의 한 트리거가 무한 루프를 시작하면 중첩 수준이 초과되고 트리거가 취소됩니다. Transact-SQL 트리거에서 CLR 루틴, 유형 또는 집계를 참조하여 관리 코드를 실행하는 경우 이러한 참조는 32 수준 중첩 제한에서 한 수준으로 계산됩니다. 관리 코드 내에서 호출된 메서드는 이 제한에 따라 계산되지 않습니다.

중첩 트리거를 비활성화하려면 sp_configure의 nested triggers 옵션을 0(off)으로 설정하십시오. 기본 구성은 중첩 트리거를 허용합니다. 중첩 트리거가 해제된 경우 ALTER DATABASE를 사용하여 설정된 RECURSIVE_TRIGGERS 설정에 관계없이 재귀 트리거도 비활성화됩니다.

중첩 된 AFTER 트리거가 INSTEAD OF 트리거의 경우에도 실행 내 첫 번째는 중첩 트리거 서버 구성 옵션이 0으로 설정 됩니다. 그러나 이 설정에서는 이후의 AFTER 트리거는 발생하지 않습니다. 응용 프로그램은이 동작과 관련 된 비즈니스 규칙을 준수 하는지 확인 하는 중첩된 트리거에 대 한 응용 프로그램을 검토 하는 것이 좋습니다 때는 중첩 트리거 서버 구성 옵션을 0으로 설정 되며 한 다음 적절 하 게 수정 합니다.

지연된 이름 확인

SQL Server의 Transact-SQL 저장 프로시저, 트리거 및 일괄 처리에서는 컴파일 시간에 존재하지 않는 테이블을 참조할 수 있습니다. 이 기능을 지연된 이름 확인이라고 합니다.

DML 트리거를 만들려면 트리거를 만들 테이블이나 뷰에 대한 ALTER 권한이 필요합니다.

서버 범위(ON ALL SERVER)의 DDL 트리거 또는 LOGON 트리거를 만들려면 해당 서버에 대한 CONTROL SERVER 권한이 필요합니다. 데이터베이스 범위(ON DATABASE)의 DDL 트리거를 만들려면 현재 데이터베이스에 대한 ALTER ANY DATABASE DDL TRIGGER 권한이 필요합니다.

1. 미리 알림 메시지로 DML 트리거 사용

다음 DML 트리거는 AdventureWorks2012 데이터베이스의 Customer 테이블에 데이터를 추가하거나 변경하려고 할 때 클라이언트에 메시지를 출력합니다.

CREATE TRIGGER reminder1  
ON Sales.Customer  
AFTER INSERT, UPDATE   
AS RAISERROR ('Notify Customer Relations', 16, 10);  
GO  

2. 미리 알림 전자 메일 메시지로 DML 트리거 사용

다음 예에서는 MaryM 테이블이 변경될 때 지정한 사람(Customer)에게 전자 메일 메시지를 보냅니다.

CREATE TRIGGER reminder2  
ON Sales.Customer  
AFTER INSERT, UPDATE, DELETE   
AS  
   EXEC msdb.dbo.sp_send_dbmail  
        @profile_name = 'AdventureWorks2012 Administrator',  
        @recipients = 'danw@Adventure-Works.com',  
        @body = 'Don''t forget to print a report for the sales force.',  
        @subject = 'Reminder';  
GO  

3. PurchaseOrderHeader와 Vendor 테이블 간에 업무 규칙을 적용하는 DML AFTER 트리거 사용

CHECK 제약 조건은 열 수준 또는 테이블 수준 제약 조건이 정의된 열만 참조할 수 있으므로 모든 상호 테이블 제약 조건(이 경우 업무 규칙)을 트리거로 정의해야 합니다.

다음 예에서는 AdventureWorks2012 데이터베이스에서 DML 트리거를 만듭니다. 이 트리거가 있는지 확인 신용 등급 공급 업체는 좋은 대 한 (하지 5)에 새 구매 주문을 삽입 하려고 할 때의 PurchaseOrderHeader 테이블입니다. 공급업체의 신용 등급을 가져오려면 Vendor 테이블을 참조해야 합니다. 신용 등급이 너무 낮으면 메시지가 표시되고 삽입이 실행되지 않습니다.

-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table  
-- when the credit rating of the specified vendor is set to 5 (below average).  
  
CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader  
AFTER INSERT  
AS  
IF EXISTS (SELECT *  
           FROM Purchasing.PurchaseOrderHeader AS p   
           JOIN inserted AS i   
           ON p.PurchaseOrderID = i.PurchaseOrderID   
           JOIN Purchasing.Vendor AS v   
           ON v.BusinessEntityID = p.VendorID  
           WHERE v.CreditRating = 5  
          )  
BEGIN  
RAISERROR ('A vendor''s credit rating is too low to accept new  
purchase orders.', 16, 1);  
ROLLBACK TRANSACTION;  
RETURN   
END;  
GO  
  
-- This statement attempts to insert a row into the PurchaseOrderHeader table  
-- for a vendor that has a below average credit rating.  
-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.  
  
INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID,  
VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight)  
VALUES (  
2  
,3  
,261  
,1652  
,4  
,GETDATE()  
,GETDATE()  
,44594.55  
,3567.564  
,1114.8638 );  
GO  
  

4. 데이터베이스 범위 DDL 트리거 사용

다음 예에서는 DDL 트리거를 사용하여 데이터베이스에서 동의어가 삭제되지 않도록 방지합니다.

CREATE TRIGGER safety   
ON DATABASE   
FOR DROP_SYNONYM  
AS   
   RAISERROR ('You must disable Trigger "safety" to drop synonyms!',10, 1)  
   ROLLBACK  
GO  
DROP TRIGGER safety  
ON DATABASE;  
GO  

5. 서버 범위 DDL 트리거 사용

다음 예에서는 DDL 트리거를 사용하여 현재 서버 인스턴스에서 CREATE DATABASE 이벤트가 발생할 경우 메시지를 출력하고 EVENTDATA 함수를 사용하여 해당 Transact-SQL 문의 텍스트를 검색합니다. DDL 트리거에 EVENTDATA를 사용 하는 더 많은 예제를 참조 하십시오. EVENTDATA 함수를 사용 하 여합니다.

적용 대상: SQL Server 2008 부터 SQL Server 2016까지
CREATE TRIGGER ddl_trig_database   
ON ALL SERVER   
FOR CREATE_DATABASE   
AS   
    PRINT 'Database Created.'  
    SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')  
GO  
DROP TRIGGER ddl_trig_database  
ON ALL SERVER;  
GO  

6. LOGON 트리거 사용

에 로그인 하기 위해 시도 거부 하는 다음 로그온 트리거 예제 SQL Server 의 구성원으로는 login_test 이미 있을 경우 해당 로그인에서 실행 되는&3; 개의 사용자 세션에 로그인 합니다.

적용 대상: SQL Server 2008 부터 SQL Server 2016까지
USE master;  
GO  
CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE,  
    CHECK_EXPIRATION = ON;  
GO  
GRANT VIEW SERVER STATE TO login_test;  
GO  
CREATE TRIGGER connection_limit_trigger  
ON ALL SERVER WITH EXECUTE AS 'login_test'  
FOR LOGON  
AS  
BEGIN  
IF ORIGINAL_LOGIN()= 'login_test' AND  
    (SELECT COUNT(*) FROM sys.dm_exec_sessions  
            WHERE is_user_process = 1 AND  
                original_login_name = 'login_test') > 3  
    ROLLBACK;  
END;  
  

7. 트리거를 발생시킨 이벤트 보기

다음 예에서는 sys.triggerssys.trigger_events 카탈로그 뷰를 쿼리하여 safety 트리거를 발생시킨 Transact-SQL 언어 이벤트를 확인합니다. safety는 이전 예에서 만들었습니다.

SELECT TE.*  
FROM sys.trigger_events AS TE  
JOIN sys.triggers AS T ON T.object_id = TE.object_id  
WHERE T.parent_class = 0 AND T.name = 'safety';  
GO  

ALTER TABLE (TRANSACT-SQL)
ALTER TRIGGER (TRANSACT-SQL)
COLUMNS_UPDATED (TRANSACT-SQL)
테이블 (TRANSACT-SQL) 만들기
DROP TRIGGER (TRANSACT-SQL)
ENABLE TRIGGER (TRANSACT-SQL)
DISABLE TRIGGER (TRANSACT-SQL)
TRIGGER_NESTLEVEL (TRANSACT-SQL)
EVENTDATA (TRANSACT-SQL)
sys.dm_sql_referenced_entities (TRANSACT-SQL)
sys.dm_sql_referencing_entities (TRANSACT-SQL)
sys.sql_expression_dependencies (TRANSACT-SQL)
sp_help (TRANSACT-SQL)
sp_helptrigger (TRANSACT-SQL)
sp_helptext (TRANSACT-SQL)
sp_rename (TRANSACT-SQL)
sp_settriggerorder (TRANSACT-SQL)
Update () (TRANSACT-SQL)
DML 트리거에 대 한 정보 가져오기
DDL 트리거에 대 한 정보 가져오기
sys.triggers (TRANSACT-SQL)
sys.trigger_events (TRANSACT-SQL)
sys.sql_modules (TRANSACT-SQL)
sys.assembly_modules (TRANSACT-SQL)
sys.server_triggers (TRANSACT-SQL)
sys.server_trigger_events (TRANSACT-SQL)
sys.server_sql_modules (TRANSACT-SQL)
sys.server_assembly_modules (TRANSACT-SQL)

커뮤니티 추가 항목

추가
표시: