Usar la función EVENTDATA

Actualizado: 5 de diciembre de 2005

La información acerca de un evento que activa un desencadenador DDL se captura mediante la función EVENTDATA. Esta función devuelve un valor xml. El esquema XML incluye información acerca de lo siguiente:

• La hora del evento.
• El Id. de proceso del sistema (SPID) de la conexión en la cual se ha ejecutado el desencadenador.
• El tipo de evento que ha activado el desencadenador.

En función del tipo de evento, el esquema incluirá información adicional, como la base de datos en la que se ha producido el evento, el objeto en el que se ha producido el evento y la instrucción Transact-SQL del evento. Para obtener más información, vea EVENTDATA (Transact-SQL).

Por ejemplo, el siguiente desencadenador DDL se crea en la base de datos de ejemplo AdventureWorks:

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
;

Se ejecutará la siguiente instrucción CREATE TABLE:

    CREATE TABLE NewTable (Column1 int);

La instrucción EVENTDATA() del desencadenador DDL captura el texto de la instrucción CREATE TABLE que no se admite. Esto se realiza utilizando una instrucción XQuery en los datos xml generados por EVENTDATA y recuperando el elemento <CommandText<. Para obtener más información, vea XQuery con el tipo de datos xml.

Advertencia:

EVENTDATA captura los datos de los eventos CREATE_SCHEMA, así como el >schema_element< de la definición CREATE SCHEMA correspondiente, si existe. Además, EVENTDATA reconoce la definición <schema_element> como un evento aparte. Por lo tanto, un desencadenador DDL creado en un evento CREATE_SCHEMA y en un evento representado por el <schema_element> de la definición CREATE SCHEMA, puede devolver los mismos datos de evento dos veces, por ejemplo, datos TSQLCommand. Por ejemplo, considere un desencadenador DDL creado en los eventos CREATE_SCHEMA y CREATE_TABLE, y que se ejecute el siguiente lote: CREATE SCHEMA s CREATE TABLE t1 (col1 int) Si la aplicación recupera los datos TSQLCommand del evento CREATE_TABLE, tenga en cuenta que estos datos pueden aparecer dos veces: una vez cuando se produce el evento CREATE_SCHEMA y otra cuando se produce el evento CREATE_TABLE. Evite crear desencadenadores DDL en los eventos CREATE_SCHEMA y en los textos <schema_element> de cualquier definición CREATE SCHEMA correspondiente, o cree la lógica en su aplicación para que el mismo evento no se procese dos veces.

Ejemplo

Puede utilizar la función EVENTDATA para crear un registro de eventos. En el siguiente ejemplo, una tabla se crea para almacenar la información del evento. A continuación, se crea un desencadenador DDL en la base de datos actual que llena la tabla con la siguiente información siempre que tiene lugar un evento DDL en la base de datos:

• La hora del evento (mediante la función GETDATE).
• El usuario de la base de datos contra cuya sesión se ha producido el evento (mediante la función CURRENT_USER).
• El tipo de evento.
• La instrucción Transact-SQL que contenía el evento.

Una vez más, los dos últimos elementos se capturan mediante XQuery con los datos xml generados por EVENTDATA.

USE AdventureWorks;
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

[!NOTA] Para devolver datos de eventos, se recomienda utilizar el método XQuery value() en lugar del método query(). El método query() devuelve XML e instancias de retorno de carro y avance de línea (CRLF) con caracteres de escape de Y comercial en el resultado, mientras que en el método value() las instancias CRLF no están visibles en el resultado.

Un ejemplo de desencadenador DDL parecido se proporciona con la base de datos de ejemplo AdventureWorks. Para obtener el ejemplo, localice la carpeta Database Triggers mediante SQL Server Management Studio. Esta carpeta se encuentra en la carpeta Programación de la base de datos AdventureWorks. Haga clic con el botón secundario en ddlDatabseTriggerLog y seleccione Incluir desencadenador de base de datos como. De forma predeterminada, el desencadenador DDL ddlDatabseTriggerLog está deshabilitado.

Vea también

Conceptos

Diseñar desencadenadores DDL

Ayuda e información

Obtener ayuda sobre SQL Server 2005

Historial de cambios