EVENTDATA 함수 사용

  • 아티클

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

DDL 트리거를 발생시키는 이벤트에 대한 정보는 EVENTDATA 함수를 사용하여 캡처됩니다. 이 함수는 xml 값을 반환합니다. XML 스키마에는 다음 정보가 포함됩니다.

  • 이벤트의 시간

  • 트리거 실행 시 연결의 SPID(시스템 프로세스 ID)

  • 트리거를 실행한 이벤트의 유형

이벤트 유형에 따라 스키마에는 이벤트가 발생한 데이터베이스, 이벤트가 발생한 개체 및 이벤트의 Transact-SQL 문과 같은 추가 정보가 포함됩니다. 자세한 내용은 DDL 트리거를 참조 하세요.

예를 들어 AdventureWorks2022 예제 데이터베이스에서 다음 DDL 트리거가 생성된다고 가정해 보십시오.

CREATE TRIGGER safety   
ON DATABASE   
FOR CREATE_TABLE   
AS   
    PRINT 'CREATE TABLE Issued.'  
    SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')  
   RAISERROR ('New tables cannot be created in this database.', 16, 1)   
   ROLLBACK  
;

그런 다음 다음 CREATE TABLE 문을 실행합니다.

CREATE TABLE NewTable (Column1 int);

DDL 트리거의 문은 EVENTDATA() 허용되지 않는 문의 텍스트를 CREATE TABLE 캡처합니다. EVENTDATA에서 생성한 xml 데이터에 대해 XQuery 문을 사용하고 CommandText> 요소를 검색하여 <이 작업을 수행합니다. 자세한 내용은 XQuery 언어 참조(SQL Server)를 참조하세요.

주의

EVENTDATA는 CREATE_SCHEMA 이벤트의 데이터와 <해당 CREATE SCHEMA 정의의 schema_element> (있는 경우)를 캡처합니다. 또한 EVENTDATA는 schema_element> 정의를 별도의 이벤트로 인식합니다<. 따라서 CREATE_SCHEMA 이벤트와 CREATE SCHEMA 정의의 schema_element> 나타내는 <이벤트 둘 다에서 만든 DDL 트리거는 데이터와 같은 TSQLCommand 동일한 이벤트 데이터를 두 번 반환할 수 있습니다. 예를 들어 CREATE_SCHEMA 및 CREATE_TABLE 이벤트 모두에서 생성되고 다음 일괄 처리가 실행되는 DDL 트리거를 고려합니다.

CREATE SCHEMA s

CREATE TABLE t1 (col1 int)

애플리케이션이 CREATE_TABLE 이벤트의 데이터를 검색 TSQLCommand 하는 경우 이 데이터는 CREATE_SCHEMA 이벤트가 발생할 때와 CREATE_TABLE 이벤트가 발생할 때 두 번 나타날 수 있습니다. CREATE_SCHEMA 이벤트와 <해당 CREATE SCHEMA 정의의 schema_element> 텍스트 모두에서 DDL 트리거를 만들지 않거나 동일한 이벤트가 두 번 처리되지 않도록 애플리케이션에 논리를 빌드하지 않습니다.

ALTER TABLE 및 ALTER DATABASE 이벤트

ALTER_TABLE 및 ALTER_DATABASE 이벤트에 대한 이벤트 데이터에는 DDL 문의 영향을 받는 다른 개체의 이름 및 형식과 이러한 개체에 대해 수행된 작업도 포함됩니다. ALTER_TABLE 이벤트 데이터에는 ALTER TABLE 문의 영향을 받는 열, 제약 조건 또는 트리거의 이름과 영향을 받는 개체에서 수행되는 동작(만들기, 변경, 삭제, 설정 또는 해제)이 포함됩니다. ALTER DATABASE 이벤트 데이터에는 ALTER DATABASE 문의 영향을 받는 파일 또는 파일 그룹의 이름과 영향을 받는 개체에서 수행되는 동작(만들기, 변경, 또는 삭제)이 포함됩니다.

예를 들어 AdventureWorks 예제 데이터베이스에서 다음 DDL 트리거를 만듭니다.

CREATE TRIGGER ColumnChanges  
ON DATABASE   
FOR ALTER_TABLE  
AS  
-- Detect whether a column was created/altered/dropped.  
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)')  
RAISERROR ('Table schema cannot be modified in this database.', 16, 1);  
ROLLBACK;

그런 다음 제약 조건을 위반하는 다음 ALTER TABLE 문을 실행합니다.

ALTER TABLE Person.Address ALTER COLUMN ModifiedDate date;

DDL 트리거의 EVENTDATA() 문은 허용되지 않는 문의 텍스트를 ALTER TABLE 캡처합니다.

예

EVENTDATA 함수를 사용하여 이벤트 로그를 만들 수 있습니다. 다음 예제에서는 이벤트 정보를 저장하기 위해 테이블을 만듭니다. 그런 다음, 데이터베이스 수준 DDL 이벤트가 발생할 때마다 테이블을 다음 정보로 채우는 DDL 트리거가 현재 데이터베이스에 만들어집니다.

  • 이벤트 시간(GETDATE 함수 사용)입니다.

  • 이벤트가 발생한 세션에 대한 데이터베이스 사용자입니다(CURRENT_USER 함수 사용).

  • 이벤트의 형식입니다.

  • 이벤트를 구성한 Transact-SQL 문입니다.

다시 말하지만, 마지막 두 항목은 EVENTDATA에서 생성된 xml 데이터에 대해 XQuery를 사용하여 캡처됩니다.

USE AdventureWorks2022;  
GO  
CREATE TABLE ddl_log (PostTime datetime, DB_User nvarchar(100), Event nvarchar(100), TSQL nvarchar(2000));  
GO  
CREATE TRIGGER log   
ON DATABASE   
FOR DDL_DATABASE_LEVEL_EVENTS   
AS  
DECLARE @data XML  
SET @data = EVENTDATA()  
INSERT ddl_log   
   (PostTime, DB_User, Event, TSQL)   
   VALUES   
   (GETDATE(),   
   CONVERT(nvarchar(100), CURRENT_USER),   
   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),   
   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;  
GO  
--Test the trigger  
CREATE TABLE TestTable (a int)  
DROP TABLE TestTable ;  
GO  
SELECT * FROM ddl_log ;  
GO

참고 항목

이벤트 데이터를 반환하려면 query() 메서드 대신 XQuery value() 메서드를 사용하는 것이 좋습니다. query() 메서드는 XML 및 출력에서 앰퍼샌드로 이스케이프된 CR/LF(캐리지 리턴 및 줄 바꿈) 인스턴스를 반환하지만 value() 메서드는 출력에서는 볼 수 없는 CR/LF 인스턴스를 표시합니다.

이와 유사한 DDL 트리거 예가 AdventureWorks2022 예제 데이터베이스와 함께 제공됩니다. 예제를 가져오려면 SQL Server Management Studio를 사용하여 데이터베이스 트리거 폴더를 찾습니다. 이 폴더는 AdventureWorks2022 데이터베이스의 프로그래밍 기능 폴더 아래에 있습니다. ddlDatabaseTriggerLog를 마우스 오른쪽 단추로 클릭하고 데이터베이스 트리거 스크립트를 선택합니다. 기본적으로 DDL 트리거 ddlDatabaseTriggerLog 는 사용하지 않도록 설정됩니다.

참고 항목

DDL 이벤트
DDL 이벤트 그룹