CREATE TRIGGER(Transact-SQL)

적용 대상:SQL ServerAzure SQL 데이터베이스Azure SQL Managed Instance

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

DDL 트리거는 다양한 DDL(데이터 정의 언어) 이벤트에 대한 응답으로 실행됩니다. 이러한 이벤트는 주로 Transact-SQL CREATE, ALTER문 및 DROP DDL과 유사한 작업을 수행하는 특정 시스템 저장 프로시저에 해당합니다.

로그온 트리거는 사용자의 세션이 설정될 때 발생하는 이벤트에 대한 응답으로 LOGON 발생합니다. 트리거는 Transact-SQL 문으로 직접 만들거나 Microsoft .NET Framework CLR(공용 언어 런타임)에서 만들어지고 SQL Server 인스턴스에 업로드되는 어셈블리의 메서드로 만들 수 있습니다. SQL Server를 사용하면 특정 문에 대한 여러 트리거를 만들 수 있습니다.

Transact-SQL 구문 표기 규칙

문법

SQL Server 구문

테이블 또는 DELETE 뷰에 대한 INSERT또는 UPDATE문에서 트리거(DML 트리거):

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

테이블에 대한 또는 UPDATEDELETE 문에서 INSERT트리거(메모리 최적화 테이블의 DML 트리거):

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 ]

, , ALTER, DROP, REVOKEGRANTDENY또는 UPDATE 문(DDL 트리거)에서 CREATE트리거:

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 ]

LOGON 이벤트 트리거(로그온 트리거):

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 ]

Azure SQL Database 구문

테이블 또는 DELETE 뷰에 대한 INSERT또는 UPDATE문에서 트리거(DML 트리거):

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 ]

, , ALTER, DROP, REVOKEGRANTDENY또는 UPDATE STATISTICS 문(DDL 트리거)에서 CREATE트리거:

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 ]

인수

또는 ALTER

적용 대상: SQL Server 2016(13.x) SP1 이상 버전 및 Azure SQL Database

이미 있는 경우에만 트리거를 조건부로 변경합니다.

schema_name

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

trigger_name

트리거의 이름입니다. trigger_name trigger_name 시작하거나 ##시작할 #수 없다는 점을 제외하고 식별자에 대한 규칙을 따라야 합니다.

테이블 | 보기

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

데이터베이스

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

ALL SERVER

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

WITH ENCRYPTION

문의 텍스트를 가립니다 CREATE TRIGGER . 사용하면 WITH ENCRYPTION 트리거가 SQL Server 복제의 일부로 게시되지 않습니다. WITH ENCRYPTION 는 CLR 트리거에 대해 지정할 수 없습니다.

으로 실행

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

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

자세한 내용은 EXECUTE AS 절을 참조하세요.

네이티브 컴파일레이션

트리거가 고유하게 컴파일되었음을 나타냅니다.

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

스키마바인딩

트리거에서 참조되는 테이블을 삭제 또는 변경할 수 없도록 합니다.

이 옵션은 메모리 최적화 테이블의 트리거에 필요하며 기존 테이블의 트리거에는 지원되지 않습니다.

FOR | 후

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

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

대신에

트리거하는 SQL 문 대신 DML 트리거가 시작되도록 지정합니다. 즉, 트리거를 시작하는 문의 동작을 재정의합니다. DDL 또는 로그온 트리거에는 지정할 INSTEAD OF 수 없습니다.

테이블 또는 뷰에서 하나 이상의 트리거INSERTUPDATE를 정의 INSTEAD OF 할 DELETE 수 있습니다. 각 보기에 고유한 INSTEAD OF 트리거가 있는 보기에 대한 뷰를 정의할 수도 있습니다.

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

{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }

이 테이블이나 뷰에 수행될 경우 DML 트리거를 활성화하는 데이터 수정 문을 지정합니다. 옵션을 하나 이상 지정합니다. 트리거 정의에서 순서에 관계없이 해당 옵션의 조합을 사용합니다.

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

WITH APPEND

적용 대상: SQL Server 2008(10.0.x) ~ SQL Server 2008 R2(10.50.x).

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

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 이벤트 형식을 추가하여 매크로 역할을 합니다.

복제용이 아님

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

sql_statement

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

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

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

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

SELECT * FROM deleted;

자세한 내용은 삽입 및 삭제된 테이블 사용을 참조하세요.

DDL 및 로그온 트리거는 EVENTDATA 함수를 사용하여 트리거 이벤트에 대한 정보를 캡처합니다. 자세한 내용은 EVENTDATA 함수 사용을 참조하세요.

SQL Server를 사용하면 테이블 또는 뷰에서 트리거를 통해 INSTEAD OF텍스트, ntext 또는 이미지 열을 업데이트할 수 있습니다.

메모리 최적화 테이블의 트리거의 경우 최상위 수준에서 허용되는 유일한 sql_statement 블록입니다 ATOMIC . 블록 내에서 ATOMIC 허용되는 T-SQL은 네이티브 프로시지 내에서 허용되는 T-SQL에 의해 제한됩니다.

<method_specifier>

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

DML 트리거에 대한 설명

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

를 사용하여 테이블에서 실행할 첫 번째 트리거와 마지막 AFTER 트리거를 지정할 수 있습니다 sp_settriggerorder. 각각INSERTUPDATE에 대해 첫 번째 트리거와 마지막 AFTER 트리거 하나만 지정하고 DELETE 테이블에 작업을 지정할 수 있습니다. 동일한 테이블에 다른 AFTER 트리거가 있는 경우 임의로 실행됩니다.

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

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

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

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

예를 들어 트리거가 뷰에 대한 트리거로 INSTEAD OF UPDATE 정의된 경우입니다. 또한 트리거는 동일한 뷰 UPDATE 를 참조하는 문을 실행 UPDATE 합니다. 트리거에서 INSTEAD OF 시작한 문은 트리거를 다시 호출하지 않습니다. UPDATE 트리거에 의해 시작된 항목은 보기에 트리거가 없는 INSTEAD OF 것처럼 뷰에 대해 처리됩니다. 변경된 UPDATE 열은 단일 기본 테이블로 확인되어야 합니다. 기본 기본 테이블을 수정할 때마다 제약 조건을 적용하고 테이블에 대해 정의된 트리거를 발생 AFTER 시키는 체인이 시작됩니다.

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

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

트리거 제한 사항

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

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

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

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

INSTEAD OF DELETE / INSTEAD OF UPDATE 트리거는 동작에 대한 연계가 정의된 외래 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
• DROP TABLE
• DBCC DBREINDEX
• ALTER PARTITION FUNCTION
• ALTER TABLE 다음 작업을 수행하는 데 사용되는 경우:
  • 열 추가, 수정 또는 삭제
  • 파티션 전환
  • 제약 조건을 추가하거나 삭제 PRIMARY KEY 합니다 UNIQUE .

DML 트리거 최적화

트랜잭션(묵시적, 또는 그 외)에서 작업을 트리거하고, 열려 있는 동안 리소스를 잠급니다. 트랜잭션이 확인되거나 거부될 때까지 잠금은 그대로 유지됩니다( COMMIT있는 경우 ROLLBACK). 트리거 실행 기간이 길수록 다른 프로세스가 차단될 가능성이 높아집니다. 따라서 가능하면 해당 기간을 줄이도록 트리거를 작성합니다. 기간을 줄이는 한 가지 방법은 DML 문이 0개 행을 변경할 때 트리거를 해제하는 것입니다.

행을 변경하지 않는 명령에 대해 트리거를 해제하려면 ROWCOUNT_BIG 시스템 변수를 사용합니다.

다음 T-SQL 코드 조각은 행을 변경하지 않는 명령에 대해 트리거를 해제하는 방법을 보여 줍니다. 이 코드는 각 DML 트리거 시작 부분에 있어야 합니다.

IF (ROWCOUNT_BIG() = 0)
RETURN;

DDL 트리거에 대한 설명

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

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

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

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

로그온 트리거

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

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

분산 트랜잭션은 LOGON 트리거에서 지원되지 않습니다. 분산 트랜잭션이 포함된 LOGON 트리거가 실행되면 오류 3969가 반환됩니다.

로그온 트리거 사용 안 함

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

일반 트리거 고려 사항

결과 반환

이후 버전의 SQL Server에서는 트리거에서 결과를 반환하는 기능이 제거됩니다. 결과 집합을 반환하는 트리거는 작업하도록 설계되지 않은 애플리케이션에서 예기치 않은 동작을 일으킬 수 있습니다. 향후 개발 작업에서는 트리거에서 결과 집합을 반환하지 않도록 하고 현재 이를 수행하는 애플리케이션을 수정하세요. 트리거가 결과 집합을 반환하지 않도록 하려면 disallow results from triggers 옵션을 1로 설정합니다.

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

여러 트리거

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

재귀 트리거

또한 SQL Server는 설정을 사용할 때 RECURSIVE_TRIGGERS 트리거의 재귀 호출을 지원합니다 ALTER DATABASE.

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

• 간접 재귀: 간접 재귀를 사용하면 애플리케이션이 테이블을 T1업데이트합니다. 그러면 트리거가 발생 TR1하여 테이블 T2이 업데이트됩니다. 그런 다음 트리거 T2 를 실행하여 테이블을 T1업데이트합니다.

• 직접 재귀: 직접 재귀에서 애플리케이션은 테이블을 T1업데이트합니다. 그러면 트리거가 발생 TR1하여 테이블 T1이 업데이트됩니다. 테이블 T1 이 업데이트되었으므로 트리거 TR1 가 다시 발생합니다.

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

설정을 사용하지 않도록 설정하면 RECURSIVE_TRIGGERS 직접 재귀만 방지할 수 있습니다. 간접 재귀도 사용하지 않도록 설정하려면 .를 사용하여 sp_configure중첩된 트리거 서버 옵션을 0으로 설정합니다.

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

중첩된 트리거

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

중첩된 트리거를 사용하지 않도록 설정하려면 중첩된 트리거 옵션을 sp_configure 0(해제)으로 설정합니다. 기본 구성은 중첩된 트리거를 지원합니다. 중첩된 트리거가 꺼져 있으면 재귀 트리거도 RECURSIVE_TRIGGERS 비활성화 ALTER DATABASE됩니다.

중첩된 트리거 서버 구성 옵션이 0인 경우에도 트리거 내부에 INSTEAD OF 중첩된 첫 번째 AFTER 트리거가 발생합니다. 그러나 이 설정에서는 이후 AFTER 트리거가 실행되지 않습니다. 중첩 트리거에 대한 애플리케이션을 검토하여 중첩된 트리거 서버 구성 옵션이 0으로 설정된 경우 애플리케이션이 비즈니스 규칙을 따르는지 확인합니다. 해당 규칙을 따르지 않으면 적절하게 수정합니다.

지연된 이름 확인

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

사용 권한

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

서버 범위(ON ALL SERVER) 또는 로그온 트리거를 사용하여 DDL 트리거를 만들려면 서버에 대한 권한이 필요합니다 CONTROL SERVER . 데이터베이스 범위(ON DATABASE)를 사용하여 DDL 트리거를 만들려면 현재 데이터베이스에 대한 권한이 필요합니다 ALTER ANY DATABASE DDL TRIGGER .

예제

A. 미리 알림 메시지와 함께 DML 트리거 사용

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

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

B. 미리 알림 전자 메일 메시지와 함께 DML 트리거 사용

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

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

C. DML AFTER 트리거를 사용하여 PurchaseOrderHeader와 Vendor 테이블 간에 비즈니스 규칙 적용

CHECK 제약 조건은 열 수준 또는 테이블 수준 제약 조건이 정의된 열만 참조하므로 테이블 간 제약 조건(이 경우 비즈니스 규칙)을 트리거로 정의해야 합니다.

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

USE AdventureWorks2022;
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 (ROWCOUNT_BIG() = 0)
    RETURN;
    IF EXISTS (SELECT 1
        FROM inserted AS i
        INNER JOIN Purchasing.Vendor AS v
            ON v.BusinessEntityID = i.VendorID
            WHERE v.CreditRating = 5)
BEGIN
    RAISERROR ('A vendor''s credit rating is too low to accept new purchase orders.', 16, 1);
    ROLLBACK;
    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

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

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

CREATE TRIGGER safety
    ON DATABASE
    FOR DROP_SYNONYM
    AS IF (@@ROWCOUNT = 0)
           RETURN;
       RAISERROR ('You must disable Trigger "safety" to remove synonyms!', 10, 1);
       ROLLBACK;
GO

DROP TRIGGER safety
    ON DATABASE;
GO

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

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

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

F. 로그온 트리거 사용

다음 로그온 트리거 예제에서는 해당 로그인에서 실행 중인 사용자 세션이 이미 3개인 경우 로그인의 login_test 구성원으로 SQL Server에 로그인하려는 시도를 거부합니다. 강력한 암호로 변경 <password> 합니다.

USE master;
GO

CREATE LOGIN login_test
    WITH PASSWORD = '<password>' 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

G. 트리거를 발생시키는 이벤트 보기

다음 예에서는 sys.triggers 및 sys.trigger_events 카탈로그 뷰를 쿼리하여 safety 트리거를 발생시킨 Transact-SQL 언어 이벤트를 확인합니다. 트리거 safety는 예제 D에서 만들어집니다 . 데이터베이스 범위 DDL 트리거를 사용합니다.

SELECT TE.*
FROM sys.trigger_events AS TE
     INNER 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
• sys.dm_sql_referencing_entities
• sys.sql_expression_dependencies
• sp_help
• sp_helptrigger
• sp_helptext
• sp_rename
• sp_settriggerorder
• UPDATE - 트리거 함수(Transact-SQL)
• DML 트리거에 대한 정보 가져오기
• DDL 트리거에 대한 정보 가져오기
• sys.triggers
• sys.trigger_events
• sys.sql_modules
• sys.assembly_modules
• sys.server_triggers
• sys.server_trigger_events
• sys.server_sql_modules
• sys.server_assembly_modules