De functie EVENTDATA gebruiken
van toepassing op:
SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
Informatie over een gebeurtenis die een DDL-trigger activeert, wordt vastgelegd met behulp van de functie EVENTDATA. Met deze functie wordt een xml--waarde geretourneerd. Het XML-schema bevat informatie over het volgende:
De tijd van de gebeurtenis.
De systeemproces-id (SPID) van de verbinding wanneer de trigger wordt uitgevoerd.
Het type gebeurtenis dat de trigger heeft geactiveerd.
Afhankelijk van het gebeurtenistype bevat het schema vervolgens aanvullende informatie, zoals de database waarin de gebeurtenis heeft plaatsgevonden, het object waarop de gebeurtenis heeft plaatsgevonden en de Transact-SQL instructie van de gebeurtenis. Zie DDL-triggersvoor meer informatie.
De volgende DDL-trigger wordt bijvoorbeeld gemaakt in de AdventureWorks2022 voorbeelddatabase:
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
;
De volgende CREATE TABLE-instructie wordt vervolgens uitgevoerd:
CREATE TABLE NewTable (Column1 int);
De EVENTDATA()-instructie in de DDL-trigger legt de tekst vast van de CREATE TABLE-instructie die niet is toegestaan. Dit wordt bereikt door een XQuery-instructie te gebruiken op de xml- gegevens die worden gegenereerd door EVENTDATA en door het element <CommandText> op te halen. Zie XQuery Language Reference (SQL Server)voor meer informatie.
Voorzichtigheid
EVENTDATA legt de gegevens van CREATE_SCHEMA gebeurtenissen vast, evenals de <schema_element> van de bijbehorende CREATE SCHEMA-definitie, indien aanwezig. Daarnaast herkent EVENTDATA de <schema_element> definitie als een afzonderlijke gebeurtenis. Daarom kan een DDL-trigger die is gemaakt op zowel een CREATE_SCHEMA gebeurtenis als een gebeurtenis die wordt vertegenwoordigd door de <schema_element> van de definitie CREATE SCHEMA, dezelfde gebeurtenisgegevens tweemaal retourneren, zoals de TSQLCommand gegevens. Denk bijvoorbeeld aan een DDL-trigger die wordt gemaakt op zowel de CREATE_SCHEMA als CREATE_TABLE gebeurtenissen en de volgende batch wordt uitgevoerd:
CREATE SCHEMA s
CREATE TABLE t1 (col1 int)
Als de toepassing de TSQLCommand gegevens van de gebeurtenis CREATE_TABLE ophaalt, moet u er rekening mee houden dat deze gegevens twee keer kunnen worden weergegeven: wanneer de CREATE_SCHEMA gebeurtenis plaatsvindt en nogmaals wanneer de CREATE_TABLE gebeurtenis plaatsvindt. Vermijd het maken van DDL-triggers voor zowel de CREATE_SCHEMA-gebeurtenissen als de <schema_element> teksten van overeenkomstige CREATE SCHEMA-definities, of bouw logica in uw toepassing zodat dezelfde gebeurtenis niet tweemaal wordt verwerkt.
ALTER TABLE en ALTER DATABASE Events
De gebeurtenisgegevens voor de gebeurtenissen ALTER_TABLE en ALTER_DATABASE bevatten ook de namen en typen andere objecten die worden beïnvloed door de DDL-instructie en de actie die op deze objecten wordt uitgevoerd. De ALTER_TABLE gebeurtenisgegevens bevatten de namen van de kolommen, beperkingen of triggers die worden beïnvloed door de instructie ALTER TABLE en de actie (maken, wijzigen, neerzetten, inschakelen of uitschakelen) die worden uitgevoerd op de betrokken objecten. De ALTER_DATABASE gebeurtenisgegevens bevatten de namen van bestanden of bestandsgroepen die worden beïnvloed door de instructie ALTER DATABASE en de actie (maken, wijzigen of neerzetten) die worden uitgevoerd op de betrokken objecten.
Maak bijvoorbeeld de volgende DDL-trigger in de AdventureWorks-voorbeelddatabase:
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;
Voer vervolgens de volgende ALTER TABLE-instructie uit die een beperking schendt:
ALTER TABLE Person.Address ALTER COLUMN ModifiedDate date;
Met de instructie EVENTDATA() in de DDL-trigger wordt de tekst vastgelegd van de ALTER TABLE-instructie die niet is toegestaan.
Voorbeeld
U kunt de functie EVENTDATA gebruiken om een logboek met gebeurtenissen te maken. In het volgende voorbeeld wordt een tabel gemaakt om gebeurtenisgegevens op te slaan. Er wordt vervolgens een DDL-trigger gemaakt in de huidige database die de tabel vult met de volgende informatie wanneer een DDL-gebeurtenis op databaseniveau plaatsvindt:
De tijd van de gebeurtenis (met behulp van de functie GETDATE).
De databasegebruiker waarvan de sessie het evenement heeft ondergaan (met behulp van de functie CURRENT_USER).
Het type gebeurtenis.
De Transact-SQL-verklaring die deel uitmaakte van de gebeurtenis.
Ook hier worden de laatste twee items vastgelegd met behulp van XQuery op basis van de XML- gegevens die worden gegenereerd door 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
Notitie
Als u gebeurtenisgegevens wilt retourneren, wordt u aangeraden de methode XQuery value() te gebruiken in plaats van de methode query(). De methode query() retourneert XML- en ampersand-escaped regelterugloop- en CRLF-exemplaren (Line-Feed) in de uitvoer, terwijl de value() methode CRLF-exemplaren onzichtbaar maakt in de uitvoer.
Een vergelijkbaar voorbeeld van een DDL-trigger wordt geleverd met de AdventureWorks2022 voorbeelddatabase. Als u het voorbeeld wilt ophalen, zoekt u de map Databasetriggers met behulp van SQL Server Management Studio. Deze map bevindt zich onder de map Programmeerbaarheid map van de AdventureWorks2022-database. Klik met de rechtermuisknop op ddlDatabaseTriggerLog en selecteer Script Database Trigger als. DDL-trigger ddlDatabaseTriggerLog- is standaard uitgeschakeld.