다음을 통해 공유

CREATE TRIGGER(Transact-SQL)

  • 아티클

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

적용 대상: SQL Server(SQL Server 2012 - current version), Windows Azure SQL 데이터베이스(최초 릴리스 - 현재 릴리스)

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

보안 정보보안 정보

사용 권한 수준을 높이고 트리거를 실행하더라도 트리거 내의 악성 코드가 실행될 수 있습니다. 이 문제를 해결하는 방법은 트리거 보안 관리를 참조하십시오.

항목 링크 아이콘 Transact-SQL 구문 표기 규칙

구문

-- SQL Server Syntax 
Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)

CREATE TRIGGER [ schema_name . ]trigger_name 
ON { table | view } 
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF } 
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
[ 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

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

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

구문

-- Windows Azure SQL Database Syntax  
Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)

CREATE 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 ] 


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

인수

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

  • trigger_name
    트리거의 이름입니다. trigger_name은 # 또는 ##로 시작할 수 없는 경우를 제외하고 식별자에 대한 규칙을 따라야 합니다.

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

  • DATABASE
    현재 데이터베이스에 DDL 트리거의 해당 범위를 적용합니다. 이 인수를 지정하면 현재 데이터베이스에서 event_type 또는 event_group이 발생할 때마다 트리거가 실행됩니다.

  • ALL SERVER
    현재 서버에 DDL 또는 LOGON 트리거의 범위를 적용합니다. 이 인수를 지정하면 현재 서버에서 event_type 또는 event_group이 발생할 때마다 트리거가 실행됩니다.

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

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

    자세한 내용은 EXECUTE AS 절(Transact-SQL)을 참조하십시오.

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

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

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

  • INSTEAD OF
    트리거를 시작하는 SQL 문 대신 DML 트리거가 실행되도록 지정합니다. 즉, 트리거를 시작하는 문의 동작을 재정의합니다. 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 옵션을 사용할 수 없습니다.

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

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

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

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

  • 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에서 테이블 또는 뷰에 대한 INSTEAD OF 트리거를 사용하여 text, ntext 또는 image 열을 업데이트할 수 있습니다.

    중요 정보중요

    ntext, text 및 image 데이터 형식은 Microsoft SQL Server 다음 버전에서 제거될 예정입니다. 향후 개발 작업에서는 이 데이터 형식을 사용하지 않도록 하고 현재 이 데이터 형식을 사용하는 응용 프로그램은 수정하십시오. 대신 nvarchar(max), varchar(max) 및 varbinary(max)를 사용합니다. AFTER 및 INSTEAD OF 트리거는 모두 inserted 및 deleted 테이블에서 varchar(MAX), nvarchar(MAX) 및 varbinary(MAX) 데이터를 지원합니다.

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

    [!참고]

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

주의

DML 트리거

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 DATABASE

CREATE DATABASE

DROP DATABASE

RESTORE DATABASE

RESTORE LOG

RECONFIGURE

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

CREATE INDEX(CREATE SPATIAL INDEX 및 CREATE XML INDEX 포함)

ALTER INDEX

DROP INDEX

DBCC DBREINDEX

ALTER PARTITION FUNCTION

DROP TABLE

다음 용도로 사용하는 ALTER TABLE

  • 열 추가, 수정 또는 삭제

  • 파티션 전환

  • PRIMARY KEY 또는 UNIQUE 제약 조건 추가 또는 삭제

 

 

[!참고]

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

DDL 트리거

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

중요 정보중요

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

DDL 트리거에 대한 자세한 내용은 DDL 트리거를 참조하십시오.

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

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

[!참고]

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

LOGON 트리거

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

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

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

로그온 트리거 비활성화

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

일반적인 트리거 고려 사항

결과 반환

이후 버전의 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 문에만 해당되는 행이 포함됩니다.

[!참고]

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

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

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

중첩 트리거

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

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

INSTEAD OF 트리거 내부에 중첩된 첫 번째 AFTER 트리거는 nested triggers 서버 구성 옵션이 0으로 설정되어 있는 경우에도 실행됩니다. 그러나 이 설정에서는 이후의 AFTER 트리거는 발생하지 않습니다. 중첩 트리거에 대한 응용 프로그램을 검토하여 nested triggers 서버 구성 옵션이 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 트리거는 Customer 테이블에 데이터를 추가하거나 변경하려고 할 때 클라이언트에 메시지를 출력합니다.

USE AdventureWorks2012;
GO
IF OBJECT_ID ('Sales.reminder1', 'TR') IS NOT NULL
   DROP TRIGGER Sales.reminder1;
GO
CREATE TRIGGER reminder1
ON Sales.Customer
AFTER INSERT, UPDATE 
AS RAISERROR ('Notify Customer Relations', 16, 10);
GO

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

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

USE AdventureWorks2012;
GO
IF OBJECT_ID ('Sales.reminder2','TR') IS NOT NULL
    DROP TRIGGER Sales.reminder2;
GO
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 제약 조건은 열 수준 또는 테이블 수준 제약 조건이 정의된 열만 참조할 수 있으므로 모든 상호 테이블 제약 조건(이 경우 업무 규칙)을 트리거로 정의해야 합니다.

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

USE AdventureWorks2012;
GO
IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
   DROP TRIGGER Purchasing.LowCredit;
GO
-- 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 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 트리거를 사용하여 데이터베이스에서 동의어가 삭제되지 않도록 방지합니다.

USE AdventureWorks2012;
GO
IF EXISTS (SELECT * FROM sys.triggers
    WHERE parent_class = 0 AND name = 'safety')
DROP TRIGGER safety
ON DATABASE;
GO
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 함수 사용을 참조하십시오.

IF EXISTS (SELECT * FROM sys.server_triggers
    WHERE name = 'ddl_trig_database')
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO
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 트리거 사용

다음 예에서는 LOGON 트리거가 login_test 로그인의 멤버로 SQL Server에 로그인을 시도할 때 해당 로그인에서 이미 3개의 사용자 세션이 실행 중일 경우 해당 시도를 거부합니다.

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.triggers 및 sys.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)

CREATE TABLE(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)

sys.triggers(Transact-SQL)

sys.trace_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)

개념

DML 트리거에 대한 정보 가져오기

DDL 트리거에 대한 정보 가져오기