Использование функции EVENTDATA

  • Статья

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

Функция EVENTDATA позволяет получить сведения о событии, которое привело к срабатыванию триггера DDL. Эта функция возвращает значение типа xml . XML-схема содержит следующие сведения:

  • время формирования события;

  • идентификатор системного процесса (SPID), соответствующий соединению, во время которого был выполнен триггер;

  • тип события, которое привело к срабатыванию триггера.

В зависимости от типа события схема включает дополнительные сведения, такие как база данных, в которой произошло событие, объект, в котором произошло событие, и инструкция Transact-SQL события. Дополнительные сведения см. в разделе DDL Triggers.

Предположим, что в базе данных 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);

Инструкция EVENTDATA() в триггере DDL захватывает текст инструкции CREATE TABLE , что является недопустимым. Это достигается с помощью инструкции XQuery для xml-данных , создаваемых EVENTDATA, и получения <элемента CommandText> . Дополнительные сведения см. в справочнике по языку XQuery (SQL Server).

Внимание

EVENTDATA записывает данные событий CREATE_SCHEMA, а также <schema_element> соответствующего определения CREATE SCHEMA, если таковой существует. Кроме того, EVENTDATA распознает <определение schema_element> как отдельное событие. Таким образом, триггер DDL, созданный как на событии CREATE_SCHEMA, так и событие, представленное <schema_element> определения CREATE SCHEMA, может возвращать одинаковые данные события дважды, например TSQLCommand данные. Допустим, для событий CREATE_SCHEMA и CREATE_TABLE создан триггер DDL и выполняется следующий пакет:

CREATE SCHEMA s

CREATE TABLE t1 (col1 int)

Если приложение использует данные TSQLCommand о событии CREATE_TABLE, следует учитывать, что эти данные могут появиться дважды: при возникновении события CREATE_SCHEMA и при возникновении события CREATE_TABLE. Избегайте создания триггеров DDL как для событий CREATE_SCHEMA, так и <> schema_element текстов любых соответствующих определений CREATE SCHEMA или сборки логики в приложении, чтобы одно и то же событие не обрабатывалось дважды.

События 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;

Инструкция EVENTDATA() в триггере DDL захватывает текст инструкции ALTER TABLE , что является недопустимым.

Пример

Функция EVENTDATA может применяться для создания журнала событий. В следующем примере создается таблица для хранения информации о событиях. После этого для текущей базы данных создается триггер DDL, который при любом событии DDL уровня базы данных заполняет эту таблицу следующими данными:

  • время формирования события (функция GETDATE);

  • пользователь базы данных, инициировавший сеанс, во время которого произошло событие (функция CURRENT_USER);

  • тип события;

  • Инструкция Transact-SQL, состоящая из события.

Сведения о двух последних элементах регистрируются путем выполнения запроса XQuery для xml -данных, сформированных функцией EVENTDATA.

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

Примечание.

Для возврата данных о событии рекомендуется использовать метод XQuery value() , а не query() . Метод query() возвращает XML-данные, содержащие символы возврата каретки и переноса строки (CR/LF), отделенные амперсандом, а метод value() не отображает эти символы.

Аналогичный пример триггера DDL предоставляется вместе с образцом базы данных AdventureWorks2022 . Чтобы получить пример, найдите папку "Триггеры базы данных" с помощью SQL Server Management Studio. Эта папка находится в папке Programmability базы данных AdventureWorks2022. Щелкните правой кнопкой мыши ddlDatabaseTriggerLog и выберите команду Создать скрипт для триггера базы данных. По умолчанию триггер DDL ddlDatabaseTriggerLog отключен.

См. также

DDL-события
Группы DDL-событий