Usar a função EVENTDATA
As informações sobre um evento que aciona um disparador DDL são capturadas por meio da função EVENTDATA. Essa função retorna um valor xml. O esquema XML contém informações sobre o seguinte:
A hora do evento.
A ID de processo do sistema (SPID) da conexão no momento da execução do gatilho.
O tipo de evento que acionou o gatilho.
Dependendo do tipo de evento, o esquema inclui informações adicionais, como o banco de dados no qual o evento ocorreu, o objeto no qual o evento ocorreu e a instrução Transact-SQL do evento. Para obter mais informações, consulte DDL Triggers.
Por exemplo, o seguinte disparador DDL é criado no banco de dados de exemplo AdventureWorks2012 :
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
;
Em seguida, é executada a seguinte instrução CREATE TABLE :
CREATE TABLE NewTable (Column1 int);
A instrução EVENTDATA() no gatilho DDL captura o texto da instrução CREATE TABLE , que não é permitida. Isso é feito usando uma instrução XQuery em relação aos xml dados gerados por EVENTDATA e recuperando o <elemento CommandText> . Para obter mais informações, consulte Referência da linguagem XQuery (SQL Server).
Cuidado
EVENTDATA captura os dados de eventos de CREATE_SCHEMA, bem como o <schema_element> da definição CREATE SCHEMA correspondente, se houver. Além disso, EVENTDATA reconhece a <definição de schema_element> como um evento separado. Portanto, um gatilho DDL criado em um evento CREATE_SCHEMA e um evento representado pelo <schema_element> da definição CREATE SCHEMA podem retornar os mesmos dados de evento duas vezes, como os TSQLCommand dados. Por exemplo, considere um disparador DDL criado em ambos os eventos CREATE_SCHEMA e CREATE_TABLE e a execução do seguinte lote:
CREATE SCHEMA s
CREATE TABLE t1 (col1 int)
Se o aplicativo recuperar os dados de TSQLCommand do evento CREATE_TABLE, lembre-se de que esses dados podem aparecer duas vezes: primeiro, quando ocorre o evento CREATE_SCHEMA e, outra vez, quando ocorre o evento CREATE_TABLE. Evite criar gatilhos DDL nos eventos CREATE_SCHEMA e no <schema_element> textos de quaisquer definições CREATE SCHEMA correspondentes ou crie lógica em seu aplicativo para que o mesmo evento não seja processado duas vezes.
Eventos ALTER TABLE e ALTER DATABASE
Os dados de eventos para os eventos ALTER_TABLE e ALTER_DATABASE também incluem os nomes e os tipos de outros objetos afetados pela instrução DDL e pela ação executada nesses objetos. Os dados do evento ALTER_TABLE incluem os nomes das colunas, as limitações os aciondores afetados pela instrução ALTER TABLE e a ação (criar, alterar, descartar, habilitar ou desabilitar) executada nesses objetos. Os dados do evento ALTER_DATABASE incluem os nomes de qualquer arquivo ou grupos de arquivos afetados pela instução ALTER DATABASE e a ação (criar, alterar, descartar) executada nos objetos afetados.
Por exemplo, crie o seguinte gatilho DDL no banco de dados de exemplo Adventure Works:
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;
Então execute a instrução ALTER TABLE seguinte que viola uma restrição:
ALTER TABLE Person.Address ALTER COLUMN ModifiedDate date;
A instrução EVENTDATA() no gatilho DDL captura o texto da instrução ALTER TABLE , que não é permitida.
Exemplo
Você pode usar a função EVENTDATA para criar um log de eventos. No exemplo a seguir, é criada uma tabela para armazenar informações de evento. Em seguida, é criado um disparador DDL no banco de dados atual que popula a tabela com as seguintes informações, sempre que ocorre algum evento DDL em nível de banco de dados:
A hora do evento (usando a função GETDATE).
O usuário de banco de dados em relação ao qual ocorreu o evento (usando a função CURRENT_USER).
O tipo de evento.
A instrução Transact-SQL que compunham o evento.
Outra vez, os últimos dois itens são capturados pelo uso de XQuery em relação aos dados xml gerados por 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
Observação
Para retornar dados de evento, recomenda-se usar o método value() de XQuery, em vez do método query(). O método query() retorna instâncias XML e CR/LF (retorno de carro e alimentação de linha) com escape de E comercial na saída, enquanto que o método value() processa instâncias CR/LF invisíveis na saída.
Um exemplo similar de disparador DDL é fornecido com o banco de dados de exemplo AdventureWorks2012 . Para obter o exemplo, localize a pasta Gatilhos de Banco de Dados, usando o SQL Server Management Studio. Esta pasta está localizada na pasta Programação do banco de dados AdventureWorks2012 . Clique com o botão direito do mouse em ddlDatabaseTriggerLog e selecione Script de Gatilho de Banco de Dados como. Por padrão, o gatilho DDL ddlDatabaseTriggerLog está desabilitado.