Compartilhar via

Usar a função EVENTDATA

Informações sobre um evento que dispara um gatilho DDL são capturadas usando a função EVENTDATA. Essa função retorna um xml valor. O esquema XML inclui informações sobre o seguinte:

  • A hora do evento.

  • O ID do Processo do Sistema (SPID) da conexão quando o trigger foi acionado.

  • O tipo de evento que disparou 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 Gatilhos DDL.

Por exemplo, o seguinte gatilho 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, a instrução a seguir CREATE TABLE é executada:

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 é obtido 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 de 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 algum. Além disso, EVENTDATA reconhece a <definição de schema_element> como um evento separado. Portanto, um gatilho DDL criado em um evento de CREATE_SCHEMA e um evento representado pelo <schema_element> da definição CREATE SCHEMA pode retornar os mesmos dados do evento duas vezes, tais como, por exemplo, os dados do TSQLCommand evento. Por exemplo, considere um gatilho DDL criado nos eventos CREATE_SCHEMA e CREATE_TABLE e o seguinte lote é executado:

CREATE SCHEMA s

CREATE TABLE t1 (col1 int)

Se o aplicativo recuperar os TSQLCommand dados do evento CREATE_TABLE, lembre-se de que esses dados podem aparecer duas vezes: uma vez quando o evento CREATE_SCHEMA ocorrer e novamente quando o evento CREATE_TABLE ocorrer. Evite criar gatilhos DDL nos eventos CREATE_SCHEMA e nos <textos schema_element> 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.

Alterar TABELA e ALTERAR BANCO DE DADOS Eventos

Os dados do evento para os eventos ALTER_TABLE e ALTER_DATABASE também incluem os nomes e tipos de outros objetos afetados pela instrução DDL e a ação executada nesses objetos. Os dados do evento ALTER_TABLE incluem os nomes das colunas, restrições ou gatilhos afetados pela instrução ALTER TABLE e a ação (criar, alterar, soltar, habilitar ou desabilitar) executada nos objetos afetados. Os dados de evento ALTER_DATABASE incluem os nomes de arquivos ou grupos de arquivos afetados pela instrução ALTER DATABASE e a ação (criar, alterar ou soltar) executada nos objetos afetados.

Por exemplo, crie o seguinte gatilho DDL no banco de dados de exemplo AdventureWorks.

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;

Em seguida, execute a seguinte instrução ALTER TABLE 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, uma tabela é criada para armazenar informações de evento. Um gatilho DDL é criado no banco de dados atual que preenche a tabela com as seguintes informações sempre que ocorre qualquer evento DDL no nível do banco de dados:

  • A hora do evento (usando a função GETDATE).

  • O usuário do banco de dados em relação à sessão em que o evento ocorreu (usando a função CURRENT_USER).

  • O tipo de evento.

  • A instrução Transact-SQL que compunha o evento.

Novamente, os dois últimos itens são capturados usando XQuery em relação aos xml dados gerados pelo 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, recomendamos que você use o método XQuery value() em vez do query() método. O método retorna instâncias de retorno de carro e alimentação de linha (CRLF) escapadas em XML e com o caractere '&' na saída, enquanto o método torna essas instâncias CRLF invisíveis na saída.

Um exemplo de gatilho DDL semelhante é fornecido com o banco de dados 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 Gatilho de Banco de Dados de Script como. Por padrão, o gatilho DDL ddlDatabaseTriggerLog está desabilitado.

Consulte Também

Eventos DDL
Grupos de eventos DDL

  • Last updated on