Trigger DDL

  • Articolo

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di Azure

I trigger DDL vengono eseguiti in risposta a vari eventi DDL (Data Definition Language), Questi eventi corrispondono principalmente a istruzioni Transact-SQL che iniziano con le parole chiave CREATE, ALTER, DROP, GRANT, DENY, REVOKE o UPDATE STATISTICS. Alcune stored procedure di sistema che eseguono operazioni di tipo DDL possono inoltre attivare trigger DDL.

Utilizzare trigger DDL nei casi seguenti:

  • Impedire modifiche specifiche nello schema di database.

  • Fare in modo che nel database si verifichi un cambiamento in risposta a una modifica nello schema di database.

  • Registrare modifiche o eventi nello schema di database.

Importante

Testare i trigger DDL per determinarne la risposta alle stored procedure di sistema eseguite. Sia l'istruzione CREATE TYPE che la stored procedure sp_addtype , ad esempio, attivano un trigger DDL creato in un evento CREATE_TYPE.

Tipi di trigger DDL

Trigger DDL di Transact-SQL

Un tipo speciale di stored procedure Transact-SQL che esegue una o più istruzioni Transact-SQL in risposta a un evento con ambito server o con ambito database. Ad esempio, è possibile che un trigger DDL si attivi se viene eseguita un'istruzione come ALTER SERVER CONFIGURATION o se una tabella viene eliminata tramite DROP TABLE.

Trigger CLR DDL

Anziché eseguire una stored procedure Transact-SQL, un trigger CLR consente di eseguire uno o più metodi scritti in codice gestito che sono membri di un assembly creato in .NET Framework e caricato in SQL Server.

I trigger DDL vengono attivati solo in seguito all'esecuzione delle istruzioni DDL che li hanno generati I trigger DDL non possono essere utilizzati come trigger INSTEAD OF. I trigger DDL non vengono attivati in risposta a eventi che interessano stored procedure e tabelle temporanee globali o locali.

I trigger DDL non creano le tabelle inserted e deleted .

Le informazioni relative a un evento che attiva un trigger DDL e le successive modifiche provocate dal trigger vengono acquisite mediante la funzione EVENTDATA.

Più trigger da creare per ogni evento DDL.

Diversamente dai trigger DML, i trigger DDL non sono definiti a livello di ambito di schema. Pertanto, non è possibile utilizzare funzioni quali OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY e OBJECTPROPERTYEX durante l'esecuzione di query sui metadati relativi ai trigger DDL. Utilizzare in alternativa le viste del catalogo.

I trigger DDL con ambito server sono disponibili in Esplora oggetti di SQL Server Management Studio nella cartella Trigger all'interno della cartella Oggetti server . I trigger DDL con ambito database sono disponibili nella cartella Trigger database all'interno della cartella Programmabilità del database corrispondente.

Importante

L'innalzamento di livello dei privilegi consente l'esecuzione di malware all'interno dei trigger. Per altre informazioni su come limitare tale minaccia, vedere Gestione della sicurezza dei trigger.

Ambito del trigger DDL

I trigger DDL vengono attivati in risposta a un evento Transact-SQL elaborato nel database o nel server corrente. L'ambito del trigger dipende dall'evento. Ad esempio, un trigger DDL creato in modo da essere attivato in risposta a un evento CREATE_TABLE può essere attivato ogni volta che nel database o nell'istanza del server si verifica un evento CREATE_TABLE. Un trigger DDL creato in modo da essere attivato in risposta a un evento CREATE_LOGIN può essere attivato solo quando si verifica un evento CREATE_LOGIN nell'istanza del server.

Nell'esempio seguente il trigger DDL safety viene attivato ogni volta che nel database si verifica un evento DROP_TABLE o ALTER_TABLE :

CREATE TRIGGER safety   
ON DATABASE   
FOR DROP_TABLE, ALTER_TABLE   
AS   
   PRINT 'You must disable Trigger "safety" to drop or alter tables!'   
   ROLLBACK;

Nell'esempio seguente un trigger DDL consente di visualizzare un messaggio se nell'istanza corrente del server si verifica un evento CREATE_DATABASE . Nell'esempio viene utilizzata la funzione EVENTDATA per recuperare il testo dell'istruzione Transact-SQL corrispondente. Per altre informazioni sull'uso di EVENTDATA con i trigger DDL, vedere Utilizzo della funzione EVENTDATA.

IF EXISTS (SELECT * FROM sys.server_triggers  
    WHERE name = 'ddl_trig_database')  
DROP TRIGGER ddl_trig_database  
ON ALL SERVER;  
GO  
CREATE TRIGGER ddl_trig_database   
ON ALL SERVER   
FOR CREATE_DATABASE   
AS   
    PRINT 'Database Created.'  
    SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')  
GO  
DROP TRIGGER ddl_trig_database  
ON ALL SERVER;  
GO

Per accedere agli elenchi in cui viene eseguito il mapping tra le istruzioni Transact-SQL e gli ambiti che possono essere specificati per tali istruzioni, utilizzare i collegamenti disponibili nella sezione "Selezione di un'istruzione DDL specifica per l'attivazione di un trigger DDL" di seguito in questo argomento.

I trigger DDL definiti a livello di ambito di database sono archiviati come oggetti nel database in cui vengono creati. È possibile creare nel database master trigger DDL con un funzionamento analogo a quello dei trigger creati nei database progettati dall'utente. È possibile ottenere informazioni sui trigger DDL eseguendo una query sulla vista del catalogo sys.triggers . È possibile eseguire una query su sys.triggers nel contesto di database in cui vengono creati i trigger oppure specificando il nome del database come identificatore, ad esempio master.sys.triggers.

I trigger DDL definiti a livello di ambito di server sono archiviati come oggetti nel database master . È tuttavia possibile ottenere informazioni sui trigger DDL con ambito server eseguendo una query sulla vista del catalogo sys.server_triggers in qualsiasi contesto di database.

Specifica di un'istruzione o di un gruppo di istruzioni Transact-SQL

Selezione di un'istruzione DDL specifica per l'attivazione di un trigger DDL

È possibile progettare i trigger DDL in modo che vengano attivati dopo l'esecuzione di una o più istruzioni Transact-SQL specifiche. Nell'esempio precedente, il trigger safety viene attivato dopo un evento DROP_TABLE o ALTER_TABLE . Per un elenco delle istruzioni Transact-SQL che è possibile specificare per attivare un trigger e informazioni sull'ambito in cui ciascun trigger può essere attivato, vedere Eventi DDL.

Selezione di un gruppo predefinito di istruzioni DDL per l'attivazione di un trigger DDL

Un trigger DDL può essere attivato dopo l'esecuzione di qualsiasi evento Transact-SQL appartenente a un raggruppamento predefinito di eventi simili. Se, ad esempio, si desidera che un trigger DDL venga attivato dopo l'esecuzione di qualsiasi istruzione CREATE TABLE, ALTER TABLE o DROP TABLE, è possibile specificare FOR DDL_TABLE_EVENTS nell'istruzione CREATE TRIGGER. Dopo l'esecuzione di CREATE TRIGGER, gli eventi inclusi in un gruppo di eventi verranno aggiunti alla vista del catalogo sys.trigger_events .

In SQL Server 2005 (9.x), se un trigger viene creato in un gruppo di eventi, sys.trigger_events non include informazioni sul gruppo di eventi. In sys.trigger_events sono incluse solo informazioni sui singoli eventi inclusi nel gruppo in questione. In SQL Server 2008 (10.0.x) e versioni successive, sys.trigger_events rende persistenti i metadati relativi al gruppo di eventi nel quale sono creati i trigger, nonché quelli relativi ai singoli eventi inclusi nel gruppo di eventi. Le modifiche agli eventi inclusi nei gruppi di eventi in SQL Server 2008 (10.0.x) e versioni successive non vengono pertanto applicate ai trigger DDL creati in tali gruppi di eventi in SQL Server 2005 (9.x).

Per un elenco dei gruppi predefiniti di istruzioni DDL disponibili per i trigger DDL e per informazioni sulle istruzioni specifiche incluse nei gruppi di eventi e sugli ambiti in cui è possibile programmare tali gruppi, vedere Gruppi di eventi DDL.

Attività Argomento
Vengono descritte le procedure di creazione, modifica e disabilitazione dei trigger DDL. Implementare trigger DDL
Viene illustrato come creare un trigger CLR DDL. Creare trigger CLR
Viene descritto come restituire informazioni sui trigger DDL. Ottenere informazioni sui trigger DDL
Viene descritto come restituire informazioni relative a un evento che attiva un trigger DDL utilizzando la funzione EVENTDATA. Usare la funzione EVENTDATA
Viene descritto come gestire la sicurezza dei trigger. Gestire la sicurezza dei trigger

Vedi anche

Trigger DML
Trigger LOGON
CREATE TRIGGER (Transact-SQL)
EVENTDATA (Transact-SQL)