Использование функции EVENTDATA
Функция EVENTDATA позволяет получить сведения о событии, которое привело к срабатыванию триггера DDL. Эта функция возвращает значение типа xml. XML-схема содержит следующие сведения:
время формирования события;
идентификатор системного процесса (SPID), соответствующий соединению, во время которого был выполнен триггер;
тип события, которое привело к срабатыванию триггера.
В зависимости от типа события схема включает дополнительные сведения, такие как база данных, в которой произошло событие, объект, для которого произошло событие, и инструкция Transact-SQL события. Дополнительные сведения см. в разделе DDL Triggers.
Предположим, что в базе данных AdventureWorks2012 создан следующий триггер 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 AdventureWorks2012;
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-данные и символы возврата каретки и переноса строки, заэкранированные амперсандом, тогда как в выводе метода value() эти символы не экранируются.
Аналогичный пример триггера DDL предоставляется вместе с образцом базы данных AdventureWorks2012 . Для его получения перейдите в среде SQL Server Management Studioв папку с триггерами базы данных, Эта папка находится в папке Programmability базы данных AdventureWorks2012 . Щелкните правой кнопкой мыши ddlDatabaseTriggerLog и выберите команду Создать скрипт для триггера базы данных. По умолчанию триггер DDL ddlDatabaseTriggerLog отключен.