CREATE TRIGGER (Transact-SQL)

Crea un trigger DML, DDL o LOGON. Un trigger è un tipo speciale di stored procedure che viene eseguita automaticamente quando si verifica un evento nel server di database. I trigger DML vengono eseguiti quando un utente tenta di modificare dati tramite un evento DML (Data Manipulation Language). Gli eventi DML sono istruzioni INSERT, UPDATE o DELETE eseguite su una tabella o una vista.

[!NOTA]

Questi trigger vengono attivati quando viene generato un evento valido, indipendentemente dal fatto che esistano o meno righe di tabella interessate.

I trigger DDL vengono eseguiti in risposta a vari eventi DDL (Data Definition Language), Questi eventi corrispondono principalmente alle istruzioni CREATE, ALTER e DROP Transact-SQL e ad alcune stored procedure di sistema che eseguono operazioni di tipo DDL. I trigger LOGON vengono attivati in risposta all'evento LOGON generato quando viene stabilita una sessione utente. I trigger possono essere creati direttamente da istruzioni Transact-SQL oppure da metodi di assembly creati in CLR (Common Language Runtime) di Microsoft.NET Framework e caricati in un'istanza di SQL Server. SQL Server consente la creazione di più trigger per qualsiasi istruzione specifica.

Nota sulla protezione
L'alzamento di livello dei privilegi consente l'esecuzione di malware all'interno di trigger. Per ulteriori informazioni su come contrastare questa minaccia, vedere Gestione della protezione dei trigger.

Convenzioni della sintassi Transact-SQL

Sintassi

Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)
CREATE TRIGGER [ schema_name . ]trigger_name 
ON { table | view } 
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF } 
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
[ WITH APPEND ] 
[ NOT FOR REPLICATION ] 
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }

<dml_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::=
    assembly_name.class_name.method_name

Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
CREATE TRIGGER trigger_name 
ON { ALL SERVER | DATABASE } 
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<ddl_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::=
    assembly_name.class_name.method_name

Trigger on a LOGON event (Logon Trigger)
CREATE TRIGGER trigger_name 
ON ALL SERVER 
[ WITH <logon_trigger_option> [ ,...n ] ]
{ FOR| AFTER } LOGON  
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<logon_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::=
    assembly_name.class_name.method_name

Argomenti

• schema_name
  Nome dello schema a cui appartiene un trigger DML. I trigger DML sono definiti a livello di ambito di schema della tabella o della vista in cui vengono creati. Non è possibile specificare schema_name per i trigger DDL o LOGON.

• trigger_name
  Nome del trigger. I nomi dei trigger specificati in trigger_name devono essere conformi alle regole per gli identificatori, ad eccezione del fatto che trigger_name non può iniziare con # o ##.

• table | view
  Tabella o vista in cui viene eseguito il trigger DML, talvolta denominata tabella di trigger o vista di trigger. Il nome completo della tabella o della vista è facoltativo. I riferimenti alle viste possono essere utilizzati solo in trigger INSTEAD OF. Non è possibile definire trigger DML in tabelle temporanee globali o locali.

• DATABASE
  Applica l'ambito di un trigger DDL al database corrente. Se viene specificato questo parametro, il trigger viene attivato quando si verifica un tipo di evento specificato in event_type o event_group nel database corrente.

• ALL SERVER
  Applica l'ambito di un trigger DDL o LOGON al server corrente. Se viene specificato questo parametro, il trigger viene attivato quando si verifica un tipo di evento specificato in event_type o event_group nel server corrente.

• WITH ENCRYPTION
  Esegue l'offuscamento del testo dell'istruzione CREATE TRIGGER. Tramite il parametro WITH ENCRYPTION è possibile evitare la pubblicazione del trigger come parte della replica di SQL Server. Non è possibile specificare WITH ENCRYPTION per i trigger CLR.

• EXECUTE AS
  Specifica il contesto di protezione nel quale viene eseguito il trigger. Consente di controllare l'account utente utilizzato dall'istanza di SQL Server per convalidare le autorizzazioni per ogni oggetto di database a cui fa riferimento il trigger.

  Per ulteriori informazioni, vedere Clausola EXECUTE AS (Transact-SQL).

• FOR | AFTER
  AFTER specifica che il trigger DML viene attivato solo al termine dell'esecuzione di tutte le operazioni specificate nell'istruzione di trigger SQL. Affinché il trigger venga attivato, è inoltre necessario che siano stati completati tutti i controlli dei vincoli e le operazioni referenziali di propagazione.

  AFTER è il tipo di trigger predefinito quando FOR è l'unica parola chiave specificata.

  Non è possibile definire trigger AFTER per le viste.

• INSTEAD OF
  Specifica che il trigger DML viene eseguito al posto dell'istruzione di trigger SQL. Il trigger risulta pertanto prioritario rispetto alle operazioni delle istruzioni di trigger. Non è possibile specificare INSTEAD OF per i trigger DDL o LOGON.

  In una tabella o vista è possibile definire al massimo un trigger INSTEAD OF per ogni istruzione INSERT, UPDATE o DELETE. È tuttavia possibile definire viste che fanno riferimento ad altre viste. Ogni vista include un trigger INSTEAD OF.

  I trigger INSTEAD OF non sono supportati in viste aggiornabili create con la clausola WITH CHECK OPTION. Se un trigger INSTEAD OF viene aggiunto a una vista aggiornabile per la quale è stato specificato WITH CHECK OPTION, in SQL Server viene generato un errore. Per poter definire il trigger INSTEAD OF, è prima necessario rimuovere l'opzione tramite l'istruzione ALTER VIEW.

• { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
  Specifica le istruzioni di modifica dei dati che attivano il trigger DML quando vengono eseguite sulla tabella o sulla vista. È necessario specificare almeno un'opzione. Nella definizione di trigger è consentita qualsiasi combinazione delle opzioni nell'ordine desiderato.

  Per i trigger INSTEAD OF, l'opzione DELETE non è consentita in tabelle contenenti una relazione referenziale che specifica un'operazione di propagazione ON DELETE. In modo analogo, l'opzione UPDATE non è consentita in tabelle contenenti una relazione referenziale che specifica un'operazione di propagazione ON UPDATE.

• event_type
  Nome di un evento del linguaggio Transact-SQL che, dopo l'esecuzione, attiva un trigger DDL. Gli eventi supportati dai trigger DDL sono elencati in Eventi DDL.

• event_group
  Nome di un raggruppamento predefinito di eventi del linguaggio Transact-SQL. Il trigger DDL viene attivato dopo l'esecuzione di qualsiasi evento del linguaggio Transact-SQL appartenente a event_group. I gruppi di eventi supportati dai trigger DDL sono elencati in Gruppi di eventi DDL.

  Dopo il completamento dell'esecuzione di CREATE TRIGGER, event_group funge anche da macro aggiungendo i tipi di eventi che include alla vista del catalogo sys.trigger_events.

• WITH APPEND
  Specifica che deve essere aggiunto un altro trigger di un tipo già esistente. La clausola WITH APPEND non può essere utilizzata con trigger INSTEAD OF o quando il trigger AFTER viene dichiarato in modo esplicito. È possibile utilizzare la clausola WITH APPEND solo quando viene specificata la parola chiave FOR, senza INSTEAD OF o AFTER, per motivi di compatibilità con le versioni precedenti. Quando viene specificata la clausola EXTERNAL NAME, ovvero se si tratta di un trigger CLR, la clausola WITH APPEND non può essere utilizzata.

  Importante
  L'istruzione WITH APPEND verrà rimossa a partire dalla prossima versione di MicrosoftSQL Server. Non utilizzare WITH APPEND in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni che attualmente la utilizzano.

• NOT FOR REPLICATION
  Indica che il trigger non deve essere eseguito quando un agente di replica modifica la tabella coinvolta nel trigger. Per ulteriori informazioni, vedere Controllo di vincoli, identità e trigger con l'opzione NOT FOR REPLICATION.

• sql_statement
  Condizioni e azioni del trigger. Le condizioni del trigger specificano ulteriori criteri che determinano se gli eventi DML, DDL o LOGON che si tenta di eseguire avviano l'esecuzione delle azioni del trigger.

  Le azioni del trigger specificate nelle istruzioni Transact-SQL vengono attivate quando viene tentato di eseguire l'operazione.

  I trigger possono includere un numero qualsiasi di istruzioni Transact-SQL di qualunque tipo, con alcune eccezioni. Per ulteriori informazioni, vedere la sezione Osservazioni. Un trigger verifica o modifica dati in base a un'istruzione di modifica o di definizione dei dati, senza restituire dati all'utente. Le istruzioni Transact-SQL di un trigger spesso includono elementi del linguaggio per il controllo di flusso.

  I trigger DML utilizzano le tabelle logiche o concettuali Inserted e Deleted. Da un punto di vista strutturale queste tabelle sono simili alla tabella in cui viene definito il trigger, ovvero la tabella in cui si tenta di eseguire l'azione utente. Le tabelle Deleted e Inserted contengono i valori precedenti o i nuovi valori delle righe che potrebbero essere modificate dall'azione utente. Ad esempio, per recuperare tutti i valori nella tabella deleted, è possibile utilizzare il codice seguente:

  SELECT *
  FROM deleted
  

  Per ulteriori informazioni, vedere Utilizzo delle tabelle inserted e deleted.

  I trigger DDL e LOGON acquisiscono informazioni sull'evento che attiva il trigger tramite la funzione EVENTDATA (Transact-SQL). Per ulteriori informazioni, vedere Utilizzo della funzione EVENTDATA.

  In SQL Server è possibile aggiornare le colonne di tipo text, ntext o image di tabelle o viste tramite il trigger INSTEAD OF.

  Importante

  I tipi di dati ntext, text e image verranno rimossi a partire da una delle prossime versioni di MicrosoftSQL Server. Evitare di utilizzare questi tipi di dati in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni che attualmente li utilizzano. Utilizzare nvarchar(max), varchar(max) e varbinary(max) in alternativa. I trigger AFTER e INSTEAD OF supportano entrambi dati di tipo varchar(MAX), nvarchar(MAX) e varbinary(MAX) nelle tabelle Inserted e Deleted.

• < method_specifier >
  Per un trigger CLR, specifica il metodo di un assembly da associare al trigger. Il metodo deve restituire void e non deve accettare argomenti. class_name deve essere un identificatore di SQL Server valido e deve esistere come classe nell'assembly con visibilità dell'assembly. Se alla classe è stato assegnato un nome completo con lo spazio dei nomi le cui parti sono separate da un punto '.', il nome della classe deve essere delimitato tramite [ ] o " ". La classe non può essere nidificata.

  [!NOTA]

  Per impostazione predefinita, la capacità di SQL Server di eseguire il codice CLR è disattivata. È possibile creare, modificare ed eliminare oggetti di database che fanno riferimento a moduli di codice gestito, ma tali riferimenti non verranno eseguiti in un'istanza di SQL Server a meno che non si attivi l'opzione clr enabled tramite sp_configure.

Osservazioni

Trigger DML

I trigger DML spesso vengono utilizzati per l'applicazione di regole business e dell'integrità dei dati. SQL Server garantisce l'integrità referenziale dichiarativa (DRI) tramite le istruzioni ALTER TABLE e CREATE TABLE. DRI, tuttavia, non supporta l'integrità referenziale tra database. L'integrità referenziale fa riferimento alle regole riguardanti le relazioni tra le chiavi primarie ed esterne delle tabelle. Per applicare l'integrità referenziale, utilizzare i vincoli PRIMARY KEY e FOREIGN KEY in ALTER TABLE e CREATE TABLE. Gli eventuali vincoli esistenti nella tabella di trigger vengono controllati dopo l'esecuzione del trigger INSTEAD OF e prima dell'esecuzione del trigger AFTER. In caso di violazione dei vincoli, viene eseguito il rollback delle azioni del trigger INSTEAD OF e il trigger AFTER non viene attivato.

È possibile specificare il primo e l'ultimo trigger AFTER che si desidera eseguire in una tabella utilizzando sp_settriggerorder. In una tabella è possibile specificare un solo trigger AFTER da eseguire per primo e un solo trigger AFTER da eseguire per ultimo per ogni operazione INSERT, UPDATE e DELETE. Se nella stessa tabella sono inclusi altri trigger AFTER, vengono eseguiti in modo casuale.

Se il primo o l'ultimo trigger viene modificato tramite un'istruzione ALTER TRIGGER, l'attributo first (primo) o last (ultimo) impostato per il trigger modificato viene rimosso ed è necessario reimpostare il valore di ordinamento tramite sp_settriggerorder.

Un trigger AFTER viene eseguito solo dopo il completamento dell'esecuzione dell'istruzione di trigger SQL, comprese tutte le operazioni referenziali di propagazione e le verifiche di vincolo associate all'oggetto aggiornato o eliminato.

Se un trigger INSTEAD OF definito in una tabella esegue un'istruzione sulla tabella che normalmente comporterebbe una seconda attivazione del trigger INSTEAD OF, il trigger non viene chiamato in modo ricorsivo. L'istruzione viene elaborata come se la tabella non includesse un trigger INSTEAD OF e avvia la serie di operazioni sui vincoli e di esecuzioni dei trigger AFTER. Se, ad esempio, per una tabella viene definito un trigger INSTEAD OF INSERT che esegue un'istruzione INSERT sulla stessa tabella, l'istruzione INSERT eseguita dal trigger INSTEAD OF non comporta una nuova chiamata del trigger. L'istruzione INSERT eseguita dal trigger avvia il processo di esecuzione delle operazioni sui vincoli e di attivazione di tutti i trigger AFTER INSERT definiti per la tabella.

Se un trigger INSTEAD OF definito in una vista esegue un'istruzione sulla vista che normalmente comporterebbe una seconda attivazione del trigger INSTEAD OF, il trigger non viene chiamato in modo ricorsivo. L'istruzione viene risolta sotto forma di modifiche delle tabelle di base sottostanti della vista. In tal caso, la definizione della vista deve rispettare tutte le restrizioni previste per una vista aggiornabile. Per informazioni sulle viste aggiornabili, vedere Modifica di dati tramite una vista.

Se, ad esempio, per una vista viene definito un trigger INSTEAD OF UPDATE che esegue un'istruzione UPDATE che fa riferimento alla stessa vista, l'istruzione UPDATE eseguita dal trigger INSTEAD OF non comporta una nuova chiamata del trigger. L'istruzione UPDATE eseguita dal trigger viene elaborata rispetto alla vista come se la vista non includesse un trigger INSTEAD OF. Le colonne modificate da UPDATE devono essere risolte in una singola tabella di base. Ogni modifica di una tabella di base sottostante avvia il processo di applicazione dei vincoli e di attivazione dei trigger AFTER definiti per la tabella.

Test di azioni UPDATE o INSERT eseguite su colonne specifiche

È possibile progettare un trigger Transact-SQL in modo che esegua determinate azioni in base a modifiche di tipo UPDATE o INSERT in colonne specifiche. A tale scopo, utilizzare UPDATE() o COLUMNS_UPDATED nel corpo del trigger. UPDATE() consente di verificare i tentativi di esecuzione di UPDATE o INSERT su una colonna. COLUMNS_UPDATED consente di verificare operazioni UPDATE o INSERT eseguite su più colonne e restituisce uno schema di bit che indica le colonne inserite o aggiornate.

Limitazioni dei trigger

CREATE TRIGGER deve essere la prima istruzione del batch e può essere applicata a una sola tabella.

I trigger vengono creati solo nel database corrente, ma possono fare riferimento a oggetti esterni a tale database.

Se viene specificato il nome dello schema del trigger, è necessario qualificare allo stesso modo anche il nome della tabella.

All'interno di un'istruzione CREATE TRIGGER è possibile definire la stessa azione di trigger per più azioni utente, ad esempio INSERT e UPDATE.

Non è possibile definire trigger INSTEAD OF DELETE/UPDATE in una tabella con una chiave esterna per cui è stata definita un'operazione di propagazione ON DELETE/UPDATE.

In un trigger è possibile specificare qualsiasi istruzione SET. L'opzione SET scelta rimane attiva durante l'esecuzione del trigger, dopodiché viene ripristinata l'impostazione precedente.

Quando un trigger viene attivato, i risultati vengono restituiti all'applicazione chiamante, esattamente come per le stored procedure. Per impedire la restituzione di risultati a un'applicazione in seguito all'attivazione di un trigger, non includere istruzioni SELECT che restituiscono risultati o istruzioni che eseguono assegnazioni di variabili in un trigger. Un trigger contenente istruzioni SELECT che restituiscono risultati all'utente o istruzioni che eseguono assegnazioni di variabili richiede una gestione particolare. I risultati restituiti devono essere gestiti in ogni applicazione in cui sono consentite modifiche alla tabella di trigger. Se è necessario eseguire un'assegnazione di variabile in un trigger, utilizzare un'istruzione SET NOCOUNT all'inizio del trigger per impedire la restituzione dei set di risultati.

Anche se un'istruzione TRUNCATE TABLE è in effetti un'istruzione DELETE, non attiva un trigger in quanto tramite l'operazione non vengono registrate singole eliminazioni di righe. Solo gli utenti che dispongono delle autorizzazioni per eseguire un'istruzione TRUNCATE TABLE devono tuttavia fare attenzione a non eludere un trigger DELETE in questo modo.

L'istruzione WRITETEXT non attiva alcun trigger, indipendentemente dal fatto che sia registrata o meno.

Le istruzioni Transact-SQL seguenti non sono consentite in un trigger DML:

ALTER DATABASE	CREATE DATABASE	DROP DATABASE
LOAD DATABASE	LOAD LOG	RECONFIGURE
RESTORE DATABASE	RESTORE LOG

Inoltre, le istruzioni Transact-SQL seguenti non possono essere utilizzate nel corpo di un trigger DML eseguito sulla tabella o sulla vista che rappresenta la destinazione dell'azione del trigger.

CREATE INDEX (incluse CREATE SPATIAL INDEX e CREATE XML INDEX)	ALTER INDEX	DROP INDEX
DBCC DBREINDEX	ALTER PARTITION FUNCTION	DROP TABLE
ALTER TABLE quando viene utilizzata per eseguire le operazioni seguenti: Aggiungere, modificare o eliminare colonne. Passare da una partizione all'altra. Aggiungere o eliminare vincoli PRIMARY KEY o UNIQUE.

[!NOTA]

Poiché in SQL Server non è supportata l'esecuzione di trigger definiti dall'utente su tabelle di sistema, è consigliabile evitare di creare trigger definiti dall'utente per tabelle di sistema.

Trigger DDL

I trigger DDL, analogamente ai trigger standard, eseguono stored procedure in risposta a un evento. A differenza dei trigger standard, tuttavia, non vengono eseguiti in risposta a istruzioni UPDATE, INSERT o DELETE su una tabella o una vista. I trigger DDL in genere vengono eseguiti in risposta a istruzioni DDL (Data Definition Language), incluse istruzioni CREATE, ALTER, DROP, GRANT, DENY, REVOKE e UPDATE STATISTICS. Alcune stored procedure di sistema che eseguono operazioni di tipo DDL possono inoltre attivare trigger DDL.

Importante
Testare i trigger DDL per determinarne la risposta all'esecuzione delle stored procedure di sistema. Sia l'istruzione CREATE TYPE che le stored procedure sp_addtype e sp_rename, ad esempio, attivano un trigger DDL creato in un evento CREATE_TYPE.

Per ulteriori informazioni sui trigger DDL, vedere Trigger DDL.

I trigger DDL non vengono attivati in risposta a eventi che interessano stored procedure e tabelle temporanee globali o locali.

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. Per ulteriori informazioni, vedere Informazioni sui trigger DDL.

[!NOTA]

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.

Trigger LOGON

I trigger LOGON consentono di eseguire stored procedure in risposta a un evento LOGON generato quando viene stabilita una sessione utente a un'istanza di SQL Server. I trigger LOGON vengono attivati dopo il completamento della fase di autenticazione della procedura di accesso, ma prima che la sessione utente venga effettivamente stabilita. Per questo motivo, tutti i messaggi generati all'interno del trigger che verrebbero normalmente visualizzati all'utente, come i messaggi di errore e i messaggi dall'istruzione PRINT, vengono invece indirizzati al log degli errori di SQL Server. Per ulteriori informazioni, vedere Trigger LOGON.

I trigger LOGON non vengono attivati in caso di esito negativo dell'autenticazione.

In un trigger LOGON non sono supportate transazioni distribuite. Quando viene attivato un trigger LOGON contenente una transazione distribuita, viene restituito l'errore 3969.

Considerazioni generali sui trigger

Restituzione di risultati

Nelle versioni future di SQL Server, la possibilità di ottenere risultati dai trigger non sarà più disponibile. I trigger che restituiscono set di risultati possono provocare un funzionamento imprevisto delle applicazioni che non sono state progettate per il loro utilizzo. Evitarne l'utilizzo in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni in cui sono attualmente implementati. Per impedire che i trigger restituiscano set di risultati, impostare l'opzione disallow results from triggers su 1.

I trigger LOGON non consentono mai la restituzione di set di risultati e questo comportamento non è configurabile. Se un trigger LOGON genera un set di risultati, l'esecuzione del trigger ha esito negativo e viene negato il tentativo di accesso che ha attivato il trigger.

Trigger multipli

In SQL Server è possibile creare più trigger per ogni evento DML, DDL o LOGON. Se, ad esempio, viene eseguita l'istruzione CREATE TRIGGER FOR UPDATE per una tabella che dispone già di un trigger UPDATE, viene creato un ulteriore trigger di aggiornamento. Nelle versioni precedenti di SQL Server è possibile creare un solo trigger per ogni evento di modifica dei dati INSERT, UPDATE o DELETE per ogni tabella.

Trigger ricorsivi

SQL Server supporta inoltre chiamate ricorsive di trigger quando viene attivata l'impostazione RECURSIVE_TRIGGERS tramite ALTER DATABASE.

I trigger ricorsivi supportano i tipi di ricorsione seguenti:

• Ricorsione indiretta

  Quando viene utilizzata la ricorsione indiretta, un'applicazione aggiorna la tabella T1, il che attiva il trigger TR1 che aggiorna la tabella T2. In uno scenario di questo tipo viene quindi attivato il trigger T2 che aggiorna la tabella T1.

• Ricorsione diretta

  Quando viene utilizzata la ricorsione diretta, un'applicazione aggiorna la tabella T1, il che attiva il trigger TR1 che aggiorna la tabella T1. Poiché la tabella T1 è stata aggiornata, viene di nuovo attivato il trigger TR1 e il processo viene quindi ripetuto.

Nell'esempio seguente vengono utilizzati entrambi i tipi di ricorsione indiretta e diretta. Si supponga che per la tabella T1 vengano definiti due trigger di aggiornamento, TR1 e TR2. Il trigger TR1 aggiorna in modo ricorsivo la tabella T1. Un'istruzione UPDATE esegue ogni trigger TR1 e TR2 una sola volta. Inoltre, l'esecuzione di TR1 attiva l'esecuzione di TR1 in modo ricorsivo e di TR2. Le tabelle inserted e deleted per un trigger specifico includono solo le righe corrispondenti all'istruzione UPDATE che ha richiamato il trigger.

[!NOTA]

La sequenza illustrata nell'esempio precedente ha luogo solo quando viene attivata l'impostazione RECURSIVE_TRIGGERS tramite ALTER DATABASE. Non esiste un ordine prestabilito per l'esecuzione di più trigger definiti per un evento specifico. Ogni trigger deve essere autonomo.

La disattivazione di RECURSIVE_TRIGGERS consente di evitare solo la ricorsione diretta. Per disattivare anche la ricorsione indiretta, impostare l'opzione del server nested triggers su 0 utilizzando sp_configure.

Se un trigger esegue un'istruzione ROLLBACK TRANSACTION non vengono eseguiti altri trigger, indipendentemente dal livello di nidificazione.

Trigger nidificati

I trigger possono essere nidificati fino a un massimo di 32 livelli. Se un trigger modifica una tabella che include un altro trigger, viene attivato il secondo trigger, che può chiamare a sua volta un terzo trigger e così via. Se un trigger della catena attiva un ciclo infinito, viene superato il livello massimo di nidificazione e il trigger viene annullato. Quando un trigger Transact-SQL esegue codice gestito facendo riferimento a una routine, un tipo o una funzione di aggregazione CLR, questo riferimento viene conteggiato come un livello per il calcolo del limite di nidificazione massimo pari a 32 livelli. I metodi richiamati da codice gestito non vengono inclusi nel conteggio per questo limite

Per disattivare i trigger nidificati, impostare l'opzione nested triggers di sp_configure su 0 (off). Per impostazione predefinita, i trigger nidificati sono consentiti. Quando si imposta su off l'opzione relativa ai trigger nidificati, vengono disattivati anche i trigger ricorsivi, indipendentemente dall'impostazione RECURSIVE_TRIGGERS attivata tramite ALTER DATABASE.

[!NOTA]

In SQL Server 2000 un trigger AFTER nidificato in un trigger INSTEAD OF non viene attivato se l'opzione di configurazione del server nested triggers è disattivata. In SQL Server 2005 o versioni successive il primo trigger AFTER nidificato in un trigger INSTEAD OF viene attivato anche se l'opzione di configurazione del server nested triggers è impostata su 0. Tuttavia, con questa impostazione, i successivi trigger AFTER non vengono attivati. È consigliabile verificare se nelle proprie applicazioni sono presenti trigger nidificati per determinare se tali applicazioni sono comunque conformi alle regole business in uso, in relazione a questo comportamento quando l'opzione di configurazione del server nested triggers è impostata su 0, quindi apportare le modifiche eventualmente necessarie.

Risoluzione dei nomi posticipata

In SQL Server è possibile includere all'interno di stored procedure, trigger e batch Transact-SQL riferimenti a tabelle che non esistono in fase di compilazione. Questa funzionalità è denominata risoluzione dei nomi posticipata. Se, tuttavia, la stored procedure, il trigger o il batch Transact-SQL fa riferimento a una tabella definita nella stored procedure o nel trigger, in fase di creazione viene visualizzato un messaggio di avviso solo quando il livello di compatibilità è 65. Se viene utilizzato un batch, il messaggio di avviso viene visualizzato in fase di compilazione. In fase di esecuzione viene visualizzato un messaggio di errore se la tabella non esiste. Per ulteriori informazioni, vedere Risoluzione dei nomi posticipata e compilazione.

Autorizzazioni

Per creare un trigger DML, è necessario disporre dell'autorizzazione ALTER per la tabella o la vista in cui si desidera creare il trigger.

Per creare un trigger DDL con ambito server (ON ALL SERVER) o un trigger LOGON è necessaria l'autorizzazione CONTROL SERVER nel server. Per creare un trigger DDL con ambito database (ON DATABASE), è necessario disporre dell'autorizzazione ALTER ANY DATABASE DDL TRIGGER per il database corrente.

Esempi

A. Utilizzo di un trigger DML con un messaggio di promemoria

Il trigger DML seguente visualizza un messaggio nel client quando un qualsiasi utente tenta di aggiungere o modificare dati nella tabella Customer.

USE AdventureWorks;
GO
IF OBJECT_ID ('Sales.reminder1', 'TR') IS NOT NULL
   DROP TRIGGER Sales.reminder1;
GO
-- This trigger raises a message whenever a row is inserted or modified in Sales.Customer.

CREATE TRIGGER reminder1
ON Sales.Customer
AFTER INSERT, UPDATE 
AS RAISERROR ('Notify Customer Relations', 16, 10);
GO

B. Utilizzo di un trigger DML con un messaggio di promemoria inviato tramite posta elettronica

Nell'esempio seguente viene inviato un messaggio di promemoria tramite posta elettronica a un utente specificato (MaryM) quando viene apportata una modifica alla tabella Customer.

USE AdventureWorks;
GO
IF OBJECT_ID ('Sales.reminder2','TR') IS NOT NULL
    DROP TRIGGER Sales.reminder2;
GO
-- This trigger sends an e-mail message to a specified account whenever
-- a row is inserted, updated or deleted from the Sales.Customer table.

CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE 
AS
   EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'AdventureWorks Administrator',
        @recipients = 'danw@Adventure-Works.com',
        @body = 'Don''t forget to print a report for the sales force.',
        @subject = 'Reminder';
GO

C. Utilizzo di un trigger DML AFTER per applicare una regola business tra le tabelle PurchaseOrderHeader e Vendor

Poiché i vincoli CHECK possono fare riferimento solo alle colonne in cui è definito il vincolo a livello di colonna o di tabella, è necessario definire come trigger qualsiasi vincolo tra tabelle, in questo caso le regole business.

Nell'esempio seguente viene creato un trigger DML. Questo trigger verifica che la posizione creditizia del fornitore sia buona quando viene eseguito un tentativo di inserire un nuovo ordine di acquisto nella tabella PurchaseOrderHeader. Per ottenere la posizione creditizia del fornitore, è necessario fare riferimento alla tabella Vendor. Se la posizione creditizia è troppo bassa, viene visualizzato un messaggio e l'operazione di inserimento non viene eseguita.

IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
   DROP TRIGGER Purchasing.LowCredit;
GO
-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).

CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader
AFTER INSERT
AS
IF EXISTS (SELECT *
           FROM Purchasing.PurchaseOrderHeader p 
           JOIN inserted AS i 
           ON p.PurchaseOrderID = i.PurchaseOrderID 
           JOIN Purchasing.Vendor AS v 
           ON v.VendorID = p.VendorID
           WHERE v.CreditRating = 5
          )
BEGIN
RAISERROR ('This vendor''s credit rating is too low to accept new
purchase orders.', 16, 1);
ROLLBACK TRANSACTION;
RETURN 
END;

-- This statement attempts to insert a row into the PurchaseOrderHeader table
-- for a vendor that has a below average credit rating.
-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.

INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID,
VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight)
VALUES(
2
,3
,261    
,1652   
,4  
,GETDATE()
,GETDATE()
,44594.55   
,3567.564   
,1114.8638);
GO

D. Utilizzo della risoluzione dei nomi posticipata

Nell'esempio seguente vengono creati due trigger DML per illustrare la risoluzione dei nomi posticipata.

USE AdventureWorks;
GO
IF OBJECT_ID ('HumanResources.trig1','TR') IS NOT NULL
   DROP TRIGGER HumanResources.trig1;
GO
-- Creating a trigger on a nonexistent table.
CREATE TRIGGER HumanResources.trig1
on HumanResources.Employee
AFTER INSERT, UPDATE, DELETE
AS 
   SELECT e.EmployeeID, e.BirthDate, x.info 
   FROM HumanResources.Employee AS e INNER JOIN does_not_exist AS x 
      ON e.EmployeeID = x.xID
GO

-- This statement displays the text of the trigger.
SELECT t.object_id, m.definition
FROM sys.triggers AS t INNER JOIN sys.sql_modules AS m 
   ON t.object_id = m.object_id
WHERE t.type = 'TR' and t.name = 'trig1'
AND t.parent_class = 1
GO

-- Creating a trigger on an existing table, but with a nonexistent 
-- column.
USE AdventureWorks;
GO
IF OBJECT_ID ('HumanResources.trig2','TR') IS NOT NULL
   DROP TRIGGER HumanResources.trig2
GO

CREATE TRIGGER HumanResources.trig2 
ON HumanResources.Employee
AFTER INSERT, UPDATE
AS 
   DECLARE @fax varchar(12)
   SELECT @fax = 'AltPhone'
   FROM HumanResources.Employee
GO

-- This statement displays the text of the trigger.

SELECT t.object_id, m.definition
FROM sys.triggers AS t INNER JOIN sys.sql_modules AS m 
   ON t.object_id = m.object_id
WHERE t.type = 'TR' and t.name = 'trig2'
AND t.parent_class = 1
GO

E. Utilizzo di un trigger DDL con ambito database

Nell'esempio seguente viene utilizzato un trigger DDL per impedire l'eliminazione di qualsiasi sinonimo in un database.

USE AdventureWorks;
GO

IF EXISTS (SELECT * FROM sys.triggers
    WHERE parent_class = 0 AND name = 'safety')
DROP TRIGGER safety
ON DATABASE;
GO

CREATE TRIGGER safety 
ON DATABASE 
FOR DROP_SYNONYM
AS 
   RAISERROR ('You must disable Trigger "safety" to drop synonyms!',10, 1)
   ROLLBACK
GO

DROP TRIGGER safety
ON DATABASE;
GO

F. Utilizzo di un trigger DDL con ambito server

Nell'esempio seguente viene utilizzato un trigger DDL per visualizzare un messaggio se si verifica un evento CREATE DATABASE nell'istanza del server corrente e viene utilizzata la funzione EVENTDATA per recuperare il testo dell'istruzione Transact-SQL corrispondente.

[!NOTA]

Per ulteriori esempi di utilizzo di EVENTDATA in 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

G. Utilizzo di un trigger LOGON

Nell'esempio seguente di trigger LOGON viene negato il tentativo di accesso a SQL Server con l'account di accesso login_test se esistono già tre sessioni utente in esecuzione con tale account di accesso.

USE master;
GO

CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE,
    CHECK_EXPIRATION = ON;
GO

GRANT VIEW SERVER STATE TO login_test;
GO

CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = 'login_test') > 3
    ROLLBACK;
END;

H. Visualizzazione degli eventi che attivano un trigger

Nell'esempio seguente viene eseguita una query sulle viste del catalogo sys.triggers e sys.trigger_events per determinare gli eventi del linguaggio Transact-SQL che attivano il trigger safety. Il trigger safety è stato creato nell'esempio precedente.

SELECT TE.*
FROM sys.trigger_events AS TE
JOIN sys.triggers AS T
ON T.object_id = TE.object_id
WHERE T.parent_class = 0
AND T.name = 'safety'
GO

Cronologia modifiche

Aggiornamento del contenuto
Aggiunta una nota nella sezione relativa ai trigger nidificati delle Osservazioni che descrive una modifica del comportamento introdotta in SQL Server 2005 quando i trigger AFTER sono nidificati in un trigger INSTEAD OF.
Aggiunte informazioni nella sezione relativa ai trigger LOGON a indicare che tali trigger non supportano transazioni distribuite.
Rimossa l'istruzione errata che la stored procedure sp_rename non attiva trigger DDL.

Vedere anche

Riferimento

ALTER TABLE (Transact-SQL)

ALTER TRIGGER (Transact-SQL)

COLUMNS_UPDATED (Transact-SQL)

CREATE TABLE (Transact-SQL)

DROP TRIGGER (Transact-SQL)

ENABLE TRIGGER (Transact-SQL)

DISABLE TRIGGER (Transact-SQL)

TRIGGER_NESTLEVEL (Transact-SQL)

EVENTDATA (Transact-SQL)

sys.dm_sql_referenced_entities (Transact-SQL)

sys.dm_sql_referencing_entities (Transact-SQL)

sys.sql_expression_dependencies (Transact-SQL)

sp_help (Transact-SQL)

sp_helptrigger (Transact-SQL)

sp_helptext (Transact-SQL)

sp_rename (Transact-SQL)

sp_settriggerorder (Transact-SQL)

UPDATE() (Transact-SQL)

sys.triggers (Transact-SQL)

sys.trigger_events (Transact-SQL)

sys.sql_modules (Transact-SQL)

sys.assembly_modules (Transact-SQL)

sys.server_triggers (Transact-SQL)

sys.server_trigger_events (Transact-SQL)

sys.server_sql_modules (Transact-SQL)

sys.server_assembly_modules (Transact-SQL)

Concetti

Programmazione di trigger CLR

Utilizzo degli identificatori come nomi di oggetti

Informazioni sui trigger DML

Informazioni sui trigger DDL

Controllo di vincoli, identità e trigger con l'opzione NOT FOR REPLICATION

Utilizzo di tipi di dati per valori di grandi dimensioni