Condividi tramite

Usare la funzione EVENTDATA

La funzione EVENTDATA consente di acquisire le informazioni relative a un evento che attiva un trigger DDL. Questa funzione restituisce un xml valore. XML Schema include informazioni relative a:

  • Ora dell'evento.

  • ID di processo di sistema (SPID) della connessione al momento in cui viene eseguito il trigger.

  • Tipo di evento che ha attivato il trigger.

In base al tipo di evento, lo schema include informazioni aggiuntive quali il database nel quale è stato generato l'evento, l'oggetto per il quale è stato generato l'evento e l'istruzione Transact-SQL dell'evento. Per altre informazioni, vedere Trigger DDL.

Ad esempio, il trigger DDL seguente viene creato nel database di esempio 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  
;

Viene quindi eseguita l'istruzione CREATE TABLE seguente:

CREATE TABLE NewTable (Column1 int);

L'istruzione EVENTDATA() nel trigger DDL acquisisce il testo dell'istruzione CREATE TABLE , che non è consentita. Questa operazione viene ottenuta usando un'istruzione XQuery sui xml dati generati da EVENTDATA e recuperando l'elemento <CommandText> . Per altre informazioni, vedere Riferimento al linguaggio XQuery (SQL Server).

Attenzione

EVENTDATA acquisisce i dati degli eventi CREATE_SCHEMA e, se presente, il contenuto di <schema_element> della definizione CREATE SCHEMA corrispondente, se presente. Riconosce inoltre la definizione <schema_element> come evento separato. Un trigger DDL creato in un evento CREATE_SCHEMA e un evento rappresentato dal contenuto di <schema_element> della definizione CREATE SCHEMA possono pertanto restituire due volte gli stessi dati di evento, ad esempio i dati TSQLCommand. Si consideri ad esempio la creazione di un trigger DDL su entrambi gli eventi CREATE_SCHEMA e CREATE_TABLE e la successiva esecuzione del batch seguente:

CREATE SCHEMA s

CREATE TABLE t1 (col1 int)

È importante tenere presente che, se l'applicazione recupera i dati TSQLCommand dell'evento CREATE_TABLE, è possibile che i dati vengano visualizzati due volte: alla generazione dell'evento CREATE_SCHEMA e di nuovo alla generazione dell'evento CREATE_TABLE. Evitare di creare trigger DDL sia per gli eventi CREATE_SCHEMA che per i testi <schema_element> di qualsiasi definizione CREATE SCHEMA corrispondente oppure compilare nell'applicazione una logica che impedisca la doppia elaborazione dello stesso evento.

Eventi di ALTER TABLE e ALTER DATABASE

I dati degli eventi ALTER_TABLE e ALTER_DATABASE includono anche i nomi e i tipi degli altri oggetti interessati dall'istruzione DDL e dall'azione eseguita su questi oggetti. I dati degli eventi ALTER_TABLE includono i nomi delle colonne, dei vincoli o dei trigger interessati dall'istruzione ALTER TABLE e dall'azione (creazione, modifica, rimozione, abilitazione o disabilitazione) eseguita su tali oggetti. I dati degli eventi ALTER_DATABASE includono i nomi di tutti i file o i filegroup interessati dall'istruzione ALTER DATABASE e dall'azione (creazione, modifica o rimozione) eseguita su tali oggetti.

Creare, ad esempio, il trigger DDL seguente nel database di esempio 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;

Eseguire quindi l'istruzione ALTER TABLE seguente che viola un vincolo:

ALTER TABLE Person.Address ALTER COLUMN ModifiedDate date;

L'istruzione EVENTDATA() nel trigger DDL acquisisce il testo dell'istruzione ALTER TABLE , che non è consentita.

Esempio

È possibile utilizzare la funzione EVENTDATA per creazione di un log eventi. Nell'esempio seguente viene creata una tabella per l'archiviazione delle informazioni relative agli eventi. Viene quindi creato nel database corrente un trigger DDL che popola la tabella con le informazioni seguenti ogni volta che viene generato un evento DDL a livello del database:

  • Ora dell'evento (tramite la funzione GETDATE).

  • Utente del database contro la cui sessione è stato generato l'evento (utilizzando la funzione CURRENT_USER).

  • Tipo di evento.

  • L'istruzione Transact-SQL che comprendeva l'evento.

Anche in questo caso, gli ultimi due elementi vengono acquisiti usando XQuery sui xml dati generati da 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

Annotazioni

Per restituire i dati dell'evento, è consigliabile usare il metodo XQuery value() anziché il query() metodo . Il metodo query() restituisce istanze di ritorno a capo XML e con escape ampersand ritorno a capo e avanzamento riga (CRLF) nell'output, mentre il metodo value() rende invisibili le istanze CRLF nell'output.

Un esempio di trigger DDL simile viene fornito con il database di esempio AdventureWorks2012 . Per visualizzare l'esempio, individuare la cartella Trigger database utilizzando SQL Server Management Studio. Questa cartella si trova nella cartella Programmabilità del database AdventureWorks2012. Fare clic con il pulsante destro del mouse su ddlDatabaseTriggerLog e scegliere Crea script per trigger database. Per impostazione predefinita, il trigger DDL ddlDatabaseTriggerLog è disabilitato.

Vedere anche

Eventi DDL
Gruppi di eventi DDL

  • Last updated on