Nota
El acceso a esta página requiere autorización. Puede intentar iniciar sesión o cambiar directorios.
El acceso a esta página requiere autorización. Puede intentar cambiar los directorios.
La información sobre un evento que desencadena un desencadenador DDL se captura mediante la función EVENTDATA. Esta función devuelve un xml valor. El esquema XML incluye información sobre lo siguiente:
La hora del evento.
Identificador de proceso del sistema (SPID) de la conexión cuando se ejecutó el desencadenador.
Tipo de evento que desencadenó el desencadenador.
Según el tipo de evento, el esquema incluye información adicional, como la base de datos en la que se produjo el evento, el objeto en el que se produjo el evento y la instrucción Transact-SQL del evento. Para obtener más información, consulte Desencadenadores DDL.
Por ejemplo, el siguiente desencadenador DDL se crea en la base de datos de ejemplo 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
;
A continuación, se ejecuta la instrucción siguiente CREATE TABLE :
CREATE TABLE NewTable (Column1 int);
La EVENTDATA() instrucción del desencadenador DDL captura el texto de la CREATE TABLE instrucción que no está permitida. Esto se logra mediante el uso de una instrucción XQuery contra los datos que genera EVENTDATA y recuperando el elemento <CommandText>. Para obtener más información, vea Referencia del lenguaje XQuery (SQL Server).
Precaución
EVENTDATA captura los datos de los eventos CREATE_SCHEMA, y también el <schema_element> de la definición CREATE SCHEMA correspondiente, si existe. Además, EVENTDATA reconoce la definición de <schema_element> como un evento independiente. Por lo tanto, un desencadenador DDL creado en un evento CREATE_SCHEMA y un evento representado por el <schema_element> de la definición CREATE SCHEMA, puede devolver los mismos datos de evento dos veces, como los TSQLCommand datos. Por ejemplo, considere un desencadenador DDL que se crea en los eventos CREATE_SCHEMA y CREATE_TABLE y se ejecuta el siguiente lote:
CREATE SCHEMA s
CREATE TABLE t1 (col1 int)
Si la aplicación recupera los TSQLCommand datos del evento CREATE_TABLE, tenga en cuenta que estos datos pueden aparecer dos veces: una vez cuando se produzca el evento CREATE_SCHEMA y de nuevo cuando se produzca el evento CREATE_TABLE. Evite crear desencadenadores DDL en los eventos de CREATE_SCHEMA y los <textos schema_element> de las definiciones CREATE SCHEMA correspondientes, o cree lógica en la aplicación para que el mismo evento no se procese dos veces.
Eventos de ALTER TABLE y ALTER DATABASE
Los datos de evento de los eventos ALTER_TABLE y ALTER_DATABASE también incluyen los nombres y tipos de otros objetos afectados por la instrucción DDL y la acción realizada en estos objetos. Los datos del evento ALTER_TABLE incluyen los nombres de las columnas, restricciones o desencadenadores afectados por la instrucción ALTER TABLE y la acción (crear, modificar, quitar, habilitar o deshabilitar) realizadas en los objetos afectados. Los datos del evento ALTER_DATABASE incluyen los nombres de los archivos o grupos de archivos afectados por la instrucción ALTER DATABASE y la acción (crear, modificar o quitar) realizadas en los objetos afectados.
Por ejemplo, cree el siguiente desencadenador DDL en la base de datos de ejemplo 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;
A continuación, ejecute la siguiente instrucción ALTER TABLE que infringe una restricción:
ALTER TABLE Person.Address ALTER COLUMN ModifiedDate date;
La instrucción EVENTDATA() del desencadenador DDL captura el texto de la instrucción ALTER TABLE que está prohibida.
Ejemplo
Puede usar la función EVENTDATA para crear un registro de eventos. En el ejemplo siguiente, se crea una tabla para almacenar información de eventos. A continuación, se crea un desencadenador DDL en la base de datos actual que rellena la tabla con la siguiente información siempre que se produzca cualquier evento DDL de nivel de base de datos:
Hora del evento (mediante la función GETDATE).
Usuario de base de datos con cuya sesión se produjo el evento (mediante la función CURRENT_USER).
El tipo de evento.
La instrucción Transact-SQL que comprendía el evento.
De nuevo, los dos últimos elementos se capturan mediante XQuery con los xml datos generados 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
Nota:
Para devolver datos de eventos, se recomienda usar el método XQuery value() en lugar del query() método . El query() método devuelve instancias XML y ampersand-escaped de carro y avance de línea (CRLF) en la salida, mientras que el value() método representa las instancias crLF invisibles en la salida.
Se proporciona un ejemplo similar de desencadenador DDL con la base de datos de ejemplo AdventureWorks2012. Para obtener el ejemplo, busque la carpeta Desencadenadores de base de datos mediante SQL Server Management Studio. Esta carpeta se encuentra en la carpeta Programmability de la base de datos AdventureWorks2012. Haga clic con el botón derecho en ddlDatabaseTriggerLog y seleccione Script Database Trigger as. De forma predeterminada, el desencadenador DDL ddlDatabaseTriggerLog está deshabilitado.