Condividi tramite

CREATE TRIGGER (Transact-SQL)

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

Crea un trigger DML, DDL o LOGON. Un trigger è una stored procedure di tipo speciale che viene eseguita automaticamente quando si verifica un evento nel server di database. I trigger DML vengono eseguiti quando un utente prova a modificare i dati tramite un evento DML (Data Manipulation Language). Gli eventi DML sono INSERTistruzioni , UPDATEo DELETE in una tabella o vista. Questi trigger vengono attivati quando viene generato un evento valido, indipendentemente dal fatto che le righe della tabella siano interessate o meno. Per altre informazioni, vedere DML Triggers.

I trigger DDL vengono eseguiti in risposta a vari eventi DDL (Data Definition Language). Questi eventi corrispondono principalmente a Transact-SQL CREATEistruzioni , ALTERe DROP e a determinate stored procedure di sistema che eseguono operazioni simili a DDL.

I trigger di accesso vengono attivati in risposta all'evento LOGON generato quando viene stabilita la sessione di un utente. È possibile generare i trigger direttamente da istruzioni Transact-SQL o da metodi di assembly creati in Common Language Runtime (CLR) di Microsoft .NET Framework e caricati in un'istanza di SQL Server. SQL Server consente di creare più trigger per ogni specifica istruzione.

Importante

L'innalzamento di livello dei privilegi consente l'esecuzione di malware all'interno dei trigger. Per altre informazioni su come attenuare questa minaccia, vedere Gestire la sicurezza dei trigger.

Nota

In questo articolo viene illustrata l'integrazione di CLR di .NET Framework in SQL Server. L'integrazione con CLR non si applica al database SQL di Azure.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

Sintassi di SQL Server

Trigger in un'istruzione INSERT, UPDATEo DELETE in una tabella o vista (trigger DML):

CREATE [ OR ALTER ] 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

Attivare in un'istruzione INSERT, UPDATEo DELETE in una tabella (trigger DML nelle tabelle ottimizzate per la memoria):

CREATE [ OR ALTER ] TRIGGER [ schema_name . ] trigger_name
ON { table }
[ WITH <dml_trigger_option> [ , ...n ] ]
{ FOR | AFTER }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS { sql_statement  [ ; ] [ , ...n ] }

<dml_trigger_option> ::=
    [ NATIVE_COMPILATION ]
    [ SCHEMABINDING ]
    [ EXECUTE AS Clause ]

Trigger in un'istruzione CREATE, ALTERDROP, DENYGRANT, REVOKEo UPDATE (trigger DDL):

CREATE [ OR ALTER ] 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 ]

Trigger in un LOGON evento (trigger di accesso):

CREATE [ OR ALTER ] 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 ]

Sintassi del database SQL di Azure

Trigger in un'istruzione INSERT, UPDATEo DELETE in una tabella o vista (trigger DML):

CREATE [ OR ALTER ] TRIGGER [ schema_name . ] trigger_name
ON { table | view }
 [ WITH <dml_trigger_option> [ , ...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
  AS { sql_statement  [ ; ] [ , ...n ] [ ; ] > }

<dml_trigger_option> ::=
        [ EXECUTE AS Clause ]

Trigger in un'istruzione CREATE, ALTERDROP, DENYGRANT, REVOKEo UPDATE STATISTICS (trigger DDL):

CREATE [ OR ALTER ] TRIGGER trigger_name
ON { DATABASE }
 [ WITH <ddl_trigger_option> [ , ...n ] ]
{ FOR | AFTER } { event_type | event_group } [ , ...n ]
AS { sql_statement  [ ; ] [ , ...n ]  [ ; ] }

<ddl_trigger_option> ::=
    [ EXECUTE AS Clause ]

Argomenti

OR ALTER

Si applica a: SQL Server 2016 (13.x) SP1 e versioni successive e database SQL di Azure

Modifica in modo condizionale il trigger solo se esiste già.

schema_name

Nome dello schema a cui appartiene un trigger DML. L'ambito dei trigger DML è definito nello schema della tabella o della vista in cui sono creati i trigger stessi. schema_name non può essere specificato per i trigger DDL o LOGON.

trigger_name

Nome del trigger. Un trigger_name deve seguire le regole per gli identificatori, ad eccezione del fatto che trigger_name non può iniziare con # o ##.

tavolo | vista

Tabella o vista nella quale viene eseguito il trigger DML. Questa tabella o vista è anche nota come tabella di trigger o vista di trigger. Il nome completo della tabella o della vista è facoltativo. È possibile fare riferimento a una visualizzazione solo tramite un INSTEAD OF trigger. Non è possibile definire trigger DML in tabelle temporanee globali o locali.

BANCA DATI

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

TUTTI I 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 event_type o event_group nel server corrente.

WITH ENCRYPTION

Nasconde il testo dell'istruzione CREATE TRIGGER . L'uso WITH ENCRYPTION di impedisce la pubblicazione del trigger come parte della replica di SQL Server. WITH ENCRYPTION non può essere specificato per i trigger CLR.

ESEGUI COME

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

Questa opzione è obbligatoria per i trigger sulle tabelle ottimizzate per la memoria.

Per altre informazioni, vedere Clausola EXECUTE AS.

Compilazione Nativa

Indica che il trigger viene compilato in modo nativo.

Questa opzione è obbligatoria per i trigger sulle tabelle ottimizzate per la memoria.

SCHEMABINDING

Assicura che le tabelle a cui si fa riferimento in un trigger non possano essere eliminate o modificate.

Questa opzione è necessaria per i trigger sulle tabelle ottimizzate per la memoria e non è supportata per i trigger su tabelle tradizionali.

FOR | DOPO

FOR oppure AFTER specifica che il trigger DML viene attivato solo quando tutte le operazioni specificate nell'istruzione SQL di attivazione sono state avviate correttamente. Perché il trigger venga attivato, è anche necessario che siano stati completati tutti i controlli dei vincoli e le operazioni referenziali di propagazione.

Non è possibile definire AFTER trigger nelle visualizzazioni.

INVECE DI

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

Al massimo, è possibile definire un INSTEAD OF trigger per INSERTogni istruzione , UPDATEo DELETE in una tabella o vista. È anche possibile definire visualizzazioni nelle visualizzazioni in cui ogni visualizzazione ha un proprio INSTEAD OF trigger.

Non è possibile definire INSTEAD OF trigger in viste aggiornabili che usano WITH CHECK OPTION. In questo modo viene generato un errore quando un INSTEAD OF trigger viene aggiunto a una vista WITH CHECK OPTION aggiornabile specificata. Questa opzione viene rimossa usando ALTER VIEW prima di definire il INSTEAD OF trigger.

{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }

Specifica le istruzioni di modifica dei dati che attivano il trigger DML quando vengono eseguite sulla tabella o sulla vista. Specificare almeno un'opzione. Usare qualsiasi combinazione delle opzioni nell'ordine desiderato nella definizione di trigger.

Per INSTEAD OF i trigger non è possibile usare l'opzione DELETE nelle tabelle con una relazione referenziale, specificando un'azione ON DELETEa catena . Analogamente, l'opzione UPDATE non è consentita nelle tabelle con una relazione referenziale, specificando un'azione ON UPDATEa catena .

WITH APPEND

Si applica a: SQL Server 2008 (10.0.x) a SQL Server 2008 R2 (10.50.x).

Specifica che deve essere aggiunto un altro trigger di un tipo già esistente. WITH APPEND non può essere usato con INSTEAD OF trigger o se un AFTER trigger è dichiarato in modo esplicito. Per la compatibilità con le versioni precedenti, usare WITH APPEND solo quando FOR viene specificato, senza INSTEAD OF o AFTER. Non è possibile specificare WITH APPEND se si usa EXTERNAL NAME , ovvero se il trigger è un trigger CLR.

event_type

Nome di un evento del linguaggio Transact-SQL che, dopo l'avvio, determina l'attivazione di 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'avvio di qualsiasi evento del linguaggio Transact-SQL incluso nel gruppo event_group. I gruppi di eventi supportati dai trigger DDL sono elencati in Gruppi di eventi DDL.

Al termine dell'esecuzione CREATE TRIGGER , event_group funge anche da macro aggiungendo i tipi di evento coperti alla vista del sys.trigger_events catalogo.

NOT FOR REPLICATION

Indica che il trigger non deve essere eseguito quando un agente di replica modifica la tabella coinvolta nel trigger.

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 prova a eseguire avviano l'esecuzione delle azioni del trigger.

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

I trigger possono includere un numero qualsiasi di istruzioni Transact-SQL di qualunque tipo, con alcune eccezioni. Per altre informazioni, vedere la sezione Note. Un trigger è progettato per controllare o modificare i dati in base a un'istruzione di modifica o definizione dei dati. Il trigger non deve 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 prova a eseguire l'azione utente. Le tabelle eliminate e inserite contengono i valori precedenti o i nuovi valori delle righe che potrebbero essere modificati dall'azione dell'utente. Ad esempio, per recuperare tutti i valori nella tabella deleted, è possibile utilizzare il codice seguente:

SELECT * FROM deleted;

Per altre informazioni, vedere Usare le tabelle inserite ed eliminate.

I trigger DDL e di accesso acquisisce informazioni sull'evento di attivazione usando la funzione EVENTDATA . Per altre informazioni, vedere Utilizzo della funzione EVENTDATA.

SQL Server consente l'aggiornamento di colonne text, ntext o image tramite il INSTEAD OF trigger in tabelle o viste.

Importante

I tipi di dati ntext, text e image verranno rimossi in una versione futura 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. Usare invece nvarchar(max), varchar(max)e varbinary(max). INSTEAD OF Entrambi AFTER i trigger supportano i dati varchar(max), nvarchar(max)e varbinary(max) nelle tabelle inserite ed eliminate.

Per i trigger nelle tabelle ottimizzate per la memoria, l'unico sql_statement consentito al livello superiore è un ATOMIC blocco. T-SQL consentito all'interno del ATOMIC blocco è limitato dal T-SQL consentito all'interno delle procedure native.

<method_specifier>

Per un trigger CLR, specifica il metodo di un assembly da associare al trigger. Il metodo non deve accettare nessun argomento e restituire void. class_name deve essere un identificatore SQL Server valido e deve essere presente come classe nell'assembly con visibilità a livello di assembly. Se la classe ha un nome completo dello spazio dei nomi che usa . per separare le parti dello spazio dei nomi, il nome della classe deve essere delimitato usando [ ] o " " delimitatori. 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 questi riferimenti non vengono eseguiti in un'istanza di SQL Server a meno che l'opzione clr enabled non sia abilitata con sp_configure.

Osservazioni per i trigger DML

I trigger DML vengono utilizzati di frequente per applicare regole business e integrità dei dati. SQL Server fornisce l'integrità referenziale dichiarativa (DRI) tramite le ALTER TABLE istruzioni 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, usare i PRIMARY KEY vincoli e FOREIGN KEY in ALTER TABLE e CREATE TABLE. Se esistono vincoli nella tabella dei trigger, vengono controllati dopo l'esecuzione del INSTEAD OF trigger e prima dell'esecuzione del AFTER trigger. Se i vincoli vengono violati, viene eseguito il rollback delle INSTEAD OF azioni del trigger e il AFTER trigger non viene attivato.

È possibile specificare i primi e gli ultimi AFTER trigger da eseguire in una tabella usando sp_settriggerorder. È possibile specificare solo un primo e un ultimo AFTER trigger per ogni INSERToperazione , UPDATEe DELETE in una tabella. Se nella stessa tabella sono presenti altri AFTER trigger, vengono eseguiti in modo casuale.

Se un'istruzione ALTER TRIGGER modifica un primo o l'ultimo trigger, il primo o l'ultimo attributo impostato sul trigger modificato viene eliminato ed è necessario reimpostare il valore dell'ordine usando sp_settriggerorder.

Un AFTER trigger viene eseguito solo dopo l'esecuzione dell'istruzione SQL di attivazione. comprese tutte le operazioni referenziali di propagazione e le verifiche di vincolo associate all'oggetto aggiornato o eliminato. Un AFTER oggetto non attiva in modo ricorsivo un INSTEAD OF trigger nella stessa tabella.

Se un INSTEAD OF trigger definito in una tabella esegue un'istruzione sulla tabella che normalmente attiva il INSTEAD OF trigger, il trigger non viene chiamato in modo ricorsivo. L'istruzione elabora invece come se la tabella non INSTEAD OF avesse trigger e avvia la catena di operazioni di vincolo ed AFTER esecuzioni di trigger. Ad esempio, se un trigger viene definito come INSTEAD OF INSERT trigger per una tabella. Inoltre, se il trigger esegue un'istruzione INSERT nella stessa tabella, l'istruzione INSERT avviata dal INSTEAD OF trigger non chiama di nuovo il trigger. Il INSERT trigger avviato dal trigger avvia il processo di esecuzione delle azioni del vincolo e genera tutti AFTER INSERT i trigger definiti per la tabella.

Quando un INSTEAD OF trigger definito in una vista esegue un'istruzione sulla vista che normalmente attiva il INSTEAD OF 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 una definizione di viste aggiornabili, vedere Modificare i dati tramite una vista.

Ad esempio, se un trigger viene definito come INSTEAD OF UPDATE trigger per una visualizzazione. Inoltre, il trigger esegue un'istruzione UPDATE che fa riferimento alla stessa visualizzazione, l'istruzione UPDATE avviata dal INSTEAD OF trigger non chiama nuovamente il trigger. L'oggetto UPDATE avviato dal trigger viene elaborato sulla vista come se la vista non disponesse di un INSTEAD OF trigger. Le colonne modificate da UPDATE devono essere risolte in una singola tabella di base. Ogni modifica a una tabella di base sottostante avvia la catena di applicazione di vincoli e attivazione AFTER dei trigger definiti per la tabella.

Testare le azioni UPDATE o INSERT in colonne specifiche

È possibile progettare un trigger Transact-SQL per eseguire determinate azioni in UPDATE base o INSERT modifiche a colonne specifiche. Usare UPDATE o COLUMNS_UPDATED nel corpo del trigger a questo scopo. UPDATE() verifica la disponibilità UPDATE o INSERT i tentativi in una colonna. COLUMNS_UPDATED verifica le UPDATE azioni o INSERT che vengono eseguite su più colonne. Questa funzione restituisce uno schema di bit che indica le colonne inserite o aggiornate.

Limitazioni dei trigger

CREATE TRIGGER deve essere la prima istruzione nel 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.

La stessa azione trigger può essere definita per più di un'azione utente (ad esempio INSERT , e UPDATE) nella stessa CREATE TRIGGER istruzione.

INSTEAD OF DELETE / INSTEAD OF UPDATE I trigger non possono essere definiti in una tabella con una chiave esterna con una catena all'azione DELETE/UPDATE definita.

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 evitare che i risultati vengano restituiti a un'applicazione a causa della generazione di un trigger, non includere istruzioni SELECT che restituiscono risultati o istruzioni che eseguono l'assegnazione di variabili in un trigger. Un trigger che include istruzioni SELECT che restituiscono risultati all'utente o alle istruzioni che eseguono l'assegnazione di variabili, richiede una gestione speciale. Sarebbe necessario scrivere i risultati restituiti in ogni applicazione in cui è consentito apportare modifiche alla tabella di trigger. Se l'assegnazione di variabili deve verificarsi in un trigger, usare un'istruzione SET NOCOUNT all'inizio del trigger per impedire la restituzione di set di risultati.

Anche se un'istruzione TRUNCATE TABLE è attiva, DELETE non attiva un trigger perché l'operazione non registra singole eliminazioni di righe. Tuttavia, solo gli utenti con autorizzazioni per eseguire un'istruzione TRUNCATE TABLE devono preoccuparsi inavvertitamente di aggirare un DELETE trigger in questo modo.

L'istruzione WRITETEXT , registrata o non registrata, non attiva un trigger.

Le istruzioni Transact-SQL riportate di seguito non sono consentite in un trigger DML:

  • ALTER DATABASE
  • CREATE DATABASE
  • DROP DATABASE
  • RESTORE DATABASE
  • RESTORE LOG
  • RECONFIGURE

Inoltre, le istruzioni Transact-SQL che seguono non sono consentite nel corpo di un trigger DML usato per la tabella o la vista che rappresenta la destinazione dell'azione del trigger.

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

Nota

Poiché SQL Server non supporta trigger definiti dall'utente nelle tabelle di sistema, è consigliabile non creare trigger definiti dall'utente nelle tabelle di sistema.

Ottimizzare i trigger DML

I trigger funzionano all'interno di transazioni (implicite o meno) e, mentre sono aperti, bloccano risorse. Il blocco rimane attivo fino a quando la transazione non viene confermata (con COMMIT) o rifiutata (con un ).ROLLBACK Più a lungo viene eseguito un trigger, maggiore è la probabilità che un altro processo venga bloccato. Scrivere quindi i trigger in modo da ridurne la durata, quando è possibile. Un modo per ottenere una durata più breve consiste nel rilasciare un trigger quando un'istruzione DML modifica zero righe.

Per rilasciare il trigger per un comando che non modifica alcuna riga, usare la variabile di sistema ROWCOUNT_BIG.

Il frammento di codice T-SQL seguente illustra come rilasciare il trigger per un comando che non modifica alcuna riga. Questo codice dovrebbe essere presente all'inizio di ogni trigger DML:

IF (ROWCOUNT_BIG() = 0)
RETURN;

Osservazioni per i trigger DDL

I trigger DDL, analogamente ai trigger standard, avviano stored procedure in risposta a un evento. A differenza dei trigger standard, tuttavia, non vengono eseguiti in risposta a UPDATEistruzioni , INSERTo DELETE in una tabella o vista. I trigger DDL in genere vengono eseguiti in risposta a istruzioni DDL (Data Definition Language). I tipi di istruzione includono CREATE, ALTERDROP, GRANT, DENY, , REVOKEe UPDATE STATISTICS. Alcune stored procedure di sistema che eseguono operazioni di tipo DDL possono anche attivare trigger DDL.

Importante

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

Per altre 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. Non è quindi possibile usare funzioni come OBJECT_ID, OBJECT_NAMEOBJECTPROPERTY, e OBJECTPROPERTYEX per eseguire query sui metadati sui trigger DDL. Utilizzare in alternativa le viste del catalogo. Per altre informazioni, vedere Ottenere 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 vengono visualizzati nella cartella Trigger di database . all'interno della cartella Programmabilità del database corrispondente.

Trigger di accesso

I trigger di accesso eseguono stored procedure in risposta a un LOGON evento. che si verifica 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 stabilita, Di conseguenza, tutti i messaggi provenienti all'interno del trigger che in genere raggiungono l'utente, ad esempio messaggi di errore e messaggi dall'istruzione PRINT , vengono deviati nel log degli errori di SQL Server. Per altre informazioni, vedere Trigger di accesso.

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.

Disabilitare un trigger di accesso

Un trigger di accesso può impedire le connessioni al motore di database per tutti gli utenti, inclusi i membri del ruolo predefinito del server sysadmin. Quando un trigger di accesso impedisce le connessioni, i membri del ruolo predefinito del server sysadmin possono connettersi usando la connessione amministrativa dedicata o avviando il motore di database in modalità di configurazione minima (-f). Per altre informazioni, vedere opzioni di avvio del servizio motore di database.

Considerazioni generali sul trigger

Risultati restituiti

Nelle versioni future di SQL Server la possibilità di ottenere risultati dai trigger non sarà più disponibile. I trigger che restituiscono set di risultati potrebbero causare comportamenti imprevisti nelle applicazioni che non sono progettati per usarli. Evitare di restituire set di risultati dai trigger in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni che attualmente li restituiscono. Per impedire che i trigger restituiscano set di risultati, impostare l'opzione disallow results from triggers (Non consentire risultati dai trigger) 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'avvio del trigger ha esito negativo e viene negato il tentativo di accesso che ha attivato il trigger.

Più trigger

SQL Server consente di creare più trigger per ogni evento DML, DDL o LOGON . Ad esempio, se CREATE TRIGGER FOR UPDATE viene eseguito per una tabella che dispone già di un UPDATE trigger, viene creato un trigger di aggiornamento aggiuntivo. Nelle versioni precedenti di SQL Server è consentito un solo trigger per ogni INSERTevento di modifica dei dati , UPDATEo DELETE per ogni tabella.

Trigger ricorsivi

SQL Server supporta anche chiamate ricorsive di trigger quando l'impostazione RECURSIVE_TRIGGERS è abilitata tramite ALTER DATABASE.

I trigger ricorsivi supportano i tipi di ricorsione seguenti:

  • Ricorsione indiretta: con ricorsione indiretta, un'applicazione aggiorna la tabella T1. Viene attivato il trigger TR1, aggiornando la tabella T2. Il trigger T2 attiva e aggiorna la tabella T1.

  • Ricorsione diretta: in ricorsione diretta, l'applicazione aggiorna la tabella T1. Viene attivato il trigger TR1, aggiornando la tabella T1. Poiché la tabella T1 è stata aggiornata, il trigger TR1 viene attivato di nuovo e così via.

Nell'esempio seguente viene usata la ricorsione di trigger indiretti e diretti Si supponga che due trigger TR1 di aggiornamento e TR2, siano definiti nella tabella T1. Attivare TR1 la tabella T1 degli aggiornamenti in modo ricorsivo. Un'istruzione UPDATE viene eseguita ogni TR1 volta TR2 . Inoltre, l'avvio di TR1 attiva l'esecuzione di TR1 (in modo ricorsivo) e TR2. Le tabelle inserite ed eliminate per un trigger specifico contengono righe che corrispondono solo all'istruzione UPDATE che ha richiamato il trigger.

Nota

Il comportamento precedente si verifica solo se l'impostazione RECURSIVE_TRIGGERS è abilitata 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 disabilitazione dell'impostazione RECURSIVE_TRIGGERS impedisce solo le ricorsioni dirette. Per disabilitare anche la ricorsione indiretta, impostare l'opzione del server trigger annidati su 0 usando sp_configure.

Se uno dei trigger esegue , ROLLBACK TRANSACTIONindipendentemente dal livello di annidamento, non vengono eseguiti altri trigger.

Trigger annidati

È possibile annidare i trigger 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 avvia il codice gestito facendo riferimento a una routine, a un tipo o a una funzione di aggregazione CLR, questo riferimento viene conteggiato come un livello per il calcolo del limite di annidamento massimo pari a 32 livelli. I metodi richiamati dal codice gestito non vengono inclusi nel conteggio per questo limite.

Per disabilitare i trigger annidati, impostare l'opzione dei trigger annidati su sp_configure 0 (disattivata). La configurazione predefinita supporta i trigger annidati. Se i trigger annidati sono disattivati, anche i trigger ricorsivi vengono disabilitati, nonostante l'impostazione RECURSIVE_TRIGGERS impostata usando ALTER DATABASE.

Il primo AFTER trigger annidato all'interno di un INSTEAD OF trigger viene attivato anche se l'opzione di configurazione del server nested triggers è 0. Ma, in questa impostazione, i trigger successivi AFTER non vengono attivati. Verificare la presenza di trigger annidati nelle applicazioni per determinare se le applicazioni seguono le regole business quando l'opzione di configurazione del server nested triggers è impostata su 0. In caso contrario, apportare le modifiche appropriate.

Risoluzione dei nomi posticipata

SQL Server consente di Transact-SQL stored procedure, trigger, funzioni e batch per fare riferimento a tabelle che non esistono in fase di compilazione. Questa funzionalità è denominata risoluzione dei nomi posticipata.

Autorizzazioni

Per creare un trigger DML, è necessaria ALTER l'autorizzazione per la tabella o la vista in cui viene creato il trigger.

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

Esempi

R. Usare un trigger DML con un messaggio di promemoria

Il trigger DML seguente stampa un messaggio al client quando chiunque tenta di aggiungere o modificare i dati nella Customer tabella nel database AdventureWorks2022.

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

B. Usare un trigger DML con un messaggio di posta elettronica di promemoria

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

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

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

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

Nell'esempio seguente viene creato un trigger DML nel AdventureWorks2022 database. Questo trigger verifica che la posizione creditizia del fornitore sia buona (non 5) quando viene eseguito un tentativo di inserimento di 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.

USE AdventureWorks2022;
GO

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 (ROWCOUNT_BIG() = 0)
    RETURN;
    IF EXISTS (SELECT 1
        FROM inserted AS i
        INNER JOIN Purchasing.Vendor AS v
            ON v.BusinessEntityID = i.VendorID
            WHERE v.CreditRating = 5)
BEGIN
    RAISERROR ('A vendor''s credit rating is too low to accept new purchase orders.', 16, 1);
    ROLLBACK;
    RETURN;
END
GO

-- 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. Usare un trigger DDL con ambito database

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

CREATE TRIGGER safety
    ON DATABASE
    FOR DROP_SYNONYM
    AS IF (@@ROWCOUNT = 0)
           RETURN;
       RAISERROR ('You must disable Trigger "safety" to remove synonyms!', 10, 1);
       ROLLBACK;
GO

DROP TRIGGER safety
    ON DATABASE;
GO

E. Usare un trigger DDL con ambito server

Nell'esempio seguente viene utilizzato un trigger DDL per stampare un messaggio se CREATE DATABASE si verifica un evento nell'istanza del server corrente e viene utilizzata la EVENTDATA funzione per recuperare il testo dell'istruzione Transact-SQL corrispondente. Per altri esempi che usano EVENTDATA nei trigger DDL, vedere Usare la funzione EVENTDATA.

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

F. Usare un trigger di accesso

Nell'esempio di trigger di accesso seguente viene negato un tentativo di accesso a SQL Server come membro dell'account login_test di accesso se sono già in esecuzione tre sessioni utente con tale account di accesso. Passare <password> a una password complessa.

USE master;
GO

CREATE LOGIN login_test
    WITH PASSWORD = '<password>' 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

G. Visualizzare gli eventi che causano l'attivazione di un trigger

Nell'esempio seguente viene eseguita una query nelle viste di catalogo sys.triggers e sys.trigger_events per determinare gli eventi del linguaggio Transact-SQL che attivano il trigger safety. Il trigger , safety, viene creato nell'esempio D. Utilizzare un trigger DDL con ambito database.

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

Contenuti correlati