Använda funktionen EVENTDATA
gäller för:
SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
Information om en händelse som utlöser en DDL-utlösare samlas in med hjälp av funktionen EVENTDATA. Den här funktionen returnerar ett XML--värde. XML-schemat innehåller information om följande:
Tidpunkten för händelsen.
Systemprocess-ID (SPID) för anslutningen när utlösaren kördes.
Typen av händelse som utlöste utlösaren.
Beroende på händelsetypen innehåller schemat sedan ytterligare information, till exempel databasen där händelsen inträffade, det objekt som händelsen inträffade mot och den Transact-SQL instruktionen för händelsen. Mer information finns i DDL-utlösare.
Till exempel skapas följande DDL-utlösare i AdventureWorks2022 exempeldatabas:
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
;
Följande CREATE TABLE-instruktion körs sedan:
CREATE TABLE NewTable (Column1 int);
EVENTDATA()-instruktionen i DDL-utlösaren avbildar texten i den CREATE TABLE-instruktion som inte är tillåten. Detta uppnås genom att använda en XQuery-instruktion mot xml- data som genereras av EVENTDATA och hämta <CommandText>-elementet. Mer information finns i SQL Server (XQuery Language Reference).
Försiktighet
EVENTDATA samlar in data för CREATE_SCHEMA händelser samt <schema_element> av motsvarande CREATE SCHEMA-definition, om det finns några. Dessutom identifierar EVENTDATA <schema_element> definition som en separat händelse. Därför kan en DDL-utlösare som skapats på både en CREATE_SCHEMA händelse och en händelse som representeras av <schema_element> i DEFINITIONEN SKAPA SCHEMA returnera samma händelsedata två gånger, till exempel TSQLCommand data. Anta till exempel att det finns en DDL-utlösare som skapas för händelserna CREATE_SCHEMA och CREATE_TABLE, och att följande batch körs:
CREATE SCHEMA s
CREATE TABLE t1 (col1 int)
Om programmet hämtar TSQLCommand data för den CREATE_TABLE händelsen bör du tänka på att dessa data kan visas två gånger: en gång när den CREATE_SCHEMA händelsen inträffar och igen när den CREATE_TABLE händelsen inträffar. Undvik att skapa DDL-utlösare för både CREATE_SCHEMA händelser och <schema_element> texterna i motsvarande CREATE SCHEMA-definitioner eller skapa logik i ditt program så att samma händelse inte bearbetas två gånger.
ALTER TABLE och ALTER DATABASE (händelser)
Händelsedata för ALTER_TABLE- och ALTER_DATABASE-händelser innehåller även namn och typer av andra objekt som påverkas av DDL-instruktionen och den åtgärd som utförs på dessa objekt. ALTER_TABLE händelsedata innehåller namnen på de kolumner, begränsningar eller utlösare som påverkas av ALTER TABLE-instruktionen och åtgärden (skapa, ändra, släppa, aktivera eller inaktivera) som utförs på de berörda objekten. ALTER_DATABASE händelsedata innehåller namnen på filer eller filgrupper som påverkas av ALTER DATABASE-instruktionen och åtgärden (skapa, ändra eller släpp) som utförs på de berörda objekten.
Skapa till exempel följande DDL-utlösare i AdventureWorks-exempeldatabasen:
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;
Kör sedan följande ALTER TABLE-instruktion som bryter mot en begränsning:
ALTER TABLE Person.Address ALTER COLUMN ModifiedDate date;
EVENTDATA()-instruktionen i DDL-utlösaren samlar in texten i den ALTER TABLE-instruktion som inte är tillåten.
Exempel
Du kan använda funktionen EVENTDATA för att skapa en logg över händelser. I följande exempel skapas en tabell för att lagra händelseinformation. En DDL-utlösare skapas sedan på den aktuella databasen som fyller tabellen med följande information när en DDL-händelse på databasnivå inträffar:
Tidpunkten för händelsen (med funktionen GETDATE).
Databasanvändaren mot vars session händelsen inträffade (med hjälp av funktionen CURRENT_USER).
Typen av händelse.
Det Transact-SQL-uttalande som utgjorde en del av händelsen.
Återigen samlas de två sista objekten in med hjälp av XQuery mot xml- data som genereras av EVENTDATA.
USE AdventureWorks2022;
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
Not
För att returnera händelsedata rekommenderar vi att du använder metoden XQuery value() i stället för metoden query(). Metoden query() returnerar XML och vagnretur- och radmatningsinstanser (CRLF) som är escape-tecknade med ampersand i utdata, medan metoden value() gör CRLF-instanser osynliga i utdata.
Ett liknande DDL-utlösarexempel tillhandahålls med AdventureWorks2022 exempeldatabas. Om du vill hämta exemplet letar du upp mappen Databasutlösare med hjälp av SQL Server Management Studio. Den här mappen finns under mappen Programmability i databasen AdventureWorks2022. Högerklicka på ddlDatabaseTriggerLog och välj Skriptdatabasutlösare som. DDL-utlösaren ddlDatabaseTriggerLog är inaktiverad som standard.