CREATE TRIGGER (Transact-SQL)

Data aggiornamento: 17 luglio 2006

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 influiscano o meno sulle righe della tabella per motivi strutturali.

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 nel Motore di database di SQL Server 2005 direttamente da istruzioni Transact-SQL oppure da metodi di assembly creati in Microsoft .NET Framework Common Language Runtime (CLR) e caricati in un'istanza di SQL Server. SQL Server consente di creare 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, con l'eccezione 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 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 validi per l'utilizzo nei trigger DDL sono elencati in Eventi DDL utilizzabili con i trigger 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 validi per l'utilizzo nei trigger DDL sono elencati in Gruppi di eventi per l'utilizzo con i trigger 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. L'utilizzo di questa clausola facoltativa è necessario solo se il livello di compatibilità è minore o uguale a 65. Se il livello di compatibilità è uguale o maggiore a 70, la clausola WITH APPEND non è necessaria per l'aggiunta di un altro trigger di un tipo esistente. Questa è l'impostazione predefinita per l'istruzione CREATE TRIGGER con un livello di compatibilità uguale o maggiore a 70. Per ulteriori informazioni, vedere sp_dbcmptlevel (Transact-SQL).

  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:
  La clausola WITH APPEND verrà rimossa a partire da una delle prossime versioni di Microsoft SQL Server. Evitarne l'utilizzo 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 deleted e inserted. 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.

  SQL Server non consente l'utilizzo all'interno di un trigger DELETE, INSERT o UPDATE di riferimenti a colonne di tipo text, ntext o image delle tabelle inserted e deleted quando il livello di compatibilità è 70. I valori di tipo text, ntext e image delle tabelle inserted e deleted non sono accessibili. Per recuperare il nuovo valore in un trigger INSERT o UPDATE, unire in join la tabella inserted con la tabella di aggiornamento originale. Quando il livello di compatibilità è minore o uguale a 65, per le colonne di tipo text, ntext o image della tabella inserted o deleted vengono restituiti valori Null se tali colonne supportano valori Null. Se non sono supportati valori Null, vengono restituite stringhe a lunghezza zero.

  Se il livello di compatibilità è maggiore o uguale a 80, 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 Microsoft SQL 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 '.', 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 su una tabella devono tuttavia fare attenzione a non eludere un trigger con un’istruzione TRUNCATE TABLE.

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.

Importante:
Nonostante sia stata introdotta in SQL Server 2005, questa restrizione viene applicata anche quando il livello di compatibilità con le versioni precedenti è 80.

CREATE 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 la stored procedure sp_addtype, ad esempio, attivano un trigger DDL creato in un evento CREATE_TYPE. La stored procedure sp_rename, invece, non attiva trigger DDL.

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 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 vengono attivati in risposta all'evento LOGON, generato quando viene stabilita una sessione utente. Per ulteriori informazioni, vedere Trigger LOGON.

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 in SQL Server 2005, 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.

[!NOTA] Quando il livello di compatibilità è 70, per impostazione predefinita l'istruzione CREATE TRIGGER aggiunge nuovi trigger a quelli esistenti se i nomi dei trigger sono diversi. Se i nomi dei trigger sono gli stessi, in SQL Server viene visualizzato un messaggio di errore. Se, tuttavia, il livello di compatibilità è minore o uguale a 65, i nuovi trigger creati con l'istruzione CREATE TRIGGER sostituiscono i trigger esistenti dello stesso tipo, anche se i nomi sono diversi. Per ulteriori informazioni, vedere sp_dbcmptlevel (Transact-SQL).

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. 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] 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.

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
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
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.

[!NOTA] Per visualizzare esempi di trigger DML AFTER che consentono di aggiornare più righe, vedere Considerazioni sulle istruzioni che interessano più righe. Per visualizzare esempi di trigger DML INSTEAD OF INSERT, vedere Trigger INSTEAD OF INSERT.

IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
   DROP TRIGGER Purchasing.LowCredit;
GO
CREATE TRIGGER LowCredit ON Purchasing.PurchaseOrderHeader
AFTER INSERT
AS
DECLARE @creditrating tinyint,
   @vendorid int
SELECT @creditrating = v.CreditRating, @vendorid = p.VendorID
FROM Purchasing.PurchaseOrderHeader AS p 
    INNER JOIN inserted AS i ON p.PurchaseOrderID =
   i.PurchaseOrderID 
    JOIN Purchasing.Vendor AS v on v.VendorID = i.VendorID
IF @creditrating = 5
BEGIN
   RAISERROR ('This vendor''s credit rating is too low to accept new
      purchase orders.', 16, 1)
ROLLBACK TRANSACTION
END
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
-- Here is the statement to actually see 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
-- Here is the statement to actually see 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

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)
sp_depends (Transact-SQL)
sys.sql_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
sys.server_trigger_events
sys.server_sql_modules
sys.server_assembly_modules (Transact-SQL)

Altre risorse

Creazione di stored procedure (Motore di database)
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

Guida in linea e informazioni

Assistenza su SQL Server 2005

Cronologia modifiche

Versione	Cronologia
12 dicembre 2006	Nuovo contenuto Aggiunta in tutto l'argomento di informazioni sui trigger LOGON introdotti in SQL Server 2005 Service Pack 2.
17 luglio 2006	Contenuto modificato Aggiornamento degli esempi E e F.
14 aprile 2006	Nuovo contenuto: Aggiunta di una nota Importante nella sezione Osservazioni in cui si consiglia di testare i trigger DDL per determinarne la risposta alle stored procedure eseguite. Aggiunta di una nota all'esempio C con collegamenti a esempi di trigger DML AFTER che consentono di aggiornare più righe e di trigger DML INSTEAD OF INSERT. Aggiunta di una nota all'esempio F con un collegamento a ulteriori esempi di utilizzo di EVENTDATA in trigger DDL. Contenuto modificato Aggiornamento dell'esempio B per l'utilizzo di Posta elettronica database.
5 dicembre 2005	Nuovo contenuto: Aggiunta di una spiegazione dell'impossibilità di definire trigger DML in tabelle temporanee globali o locali. Aggiunta dell'indicazione che i trigger DDL non vengono attivati in risposta a eventi che interessano stored procedure e tabelle temporanee globali o locali.