Trigger si traduce approssimativamente in vietnamita come grilletto, il che significa che è come un grilletto e si attiverà quando si preme il grilletto.

In SQL, un trigger è un tipo di stored procedure che viene eseguito quando si tenta di modificare i dati nella tabella in cui è stato creato il trigger. A differenza delle normali stored procedure di sistema, i trigger non possono essere eseguiti direttamente, né possono passare o ricevere parametri.

Ogni tabella avrà solitamente 3 operazioni che modificano i dati: UPDATE, INSERT, DELETE. E a volte per ciascuna di queste azioni, avremo dei vincoli sul tavolo per aiutare a preservare i dati, ora l'uso dei trigger è una buona soluzione.

Tipi di trigger

I trigger verranno attivati automaticamente quando si incontra un evento in una tabella o una vista. Le lingue degli eventi sono divise in due tipi, eventi DML e DDL. I trigger associati agli eventi DML sono chiamati trigger DML, altrimenti chiamati trigger DDL.

Trigger DML

I trigger DML vengono eseguiti al completamento di un evento DML o per conto di un evento DML. Questi trigger garantiscono l'integrità referenziale sovrapponendo le modifiche alle tabelle correlate quando viene modificata una riga.

I trigger DML sono disponibili in 3 tipi principali:

• INSERIRE grilletto
• AGGIORNAMENTO trigger
• ELIMINA trigger

Introduzione alle tabelle inserite ed eliminate

L'istruzione SQL nei trigger DML utilizza due tipi speciali di tabelle per modificare il database. Quando i dati vengono aggiunti, aggiornati o eliminati, SQL Server crea e gestisce queste tabelle automaticamente, negli Appunti vengono archiviati i dati originali e quelli modificati come segue:

Tabella inserita

La tabella inserita archivia le copie delle righe applicate tramite le istruzioni INSERT e UPDATE. Durante l'inserimento o l'aggiornamento, le nuove righe verranno aggiunte a entrambe le tabelle, alla tabella inserita e alla tabella di attivazione. La riga nella tabella inserita è una copia della nuova riga nella tabella trigger.

Tabella eliminata

La tabella Deleted archivia una copia delle righe applicate tramite le istruzioni DELETE e UPDATE. Durante l'esecuzione DELETE o UPDATE, la riga verrà eliminata dalla tabella trigger e spostata nella tabella eliminata.

Nota: la tabella inserita e la tabella eliminata sono temporanee, per archiviare memoria e controllare l'effetto di determinate modifiche ai dati e impostare le condizioni per le azioni di attivazione DML. SQL Server 2019 non consente riferimenti a colonne di testo, nxtext o immagine nelle tabelle inserite ed eliminate per i trigger AFTER .

Inserisci trigger

Un trigger INSERT viene attivato quando un nuovo record viene inserito nella tabella. Il trigger INSERT assicura che l'input sia soddisfatto ma il vincolo sia definito nella tabella.

Quando l'utente inserisce un record nella tabella, il trigger INSERT salva una copia di questi record nella tabella inserita. Quindi controlla se il valore moiws soddisfa i vincoli nella tabella.

Se il record è valido, il trigger INSERT aggiungerà la riga alla tabella, altrimenti visualizzerà un messaggio di errore. Un trigger INSERT viene creato tramite la parola chiave INSERT nelle istruzioni CREATE TRIGGER e ALTER TRIGGER.

Sintassi INSERT|UPDATE|DELETE trigger:

 CREATE TRIGGER [schema_name.]trigger_name ON [schema_name.]table_name [WITH ENCRYPTION] [FOR INSERT] AS [IF UPDATE (column_name)] [{AND|OR} UPDATE (colum_name)] AS BEGIN <sql_statement> END;

Per esempio:

Passaggio 1: creare 2 tabelle in base alle seguenti informazioni:

 CREATE TABLE Locations (LocationID int, LocName varchar(100)); CREATE TABLE LocationHistory (LocationID int, ModifedDate DATETIME);

Passaggio 2: creare un trigger INSERT denominato TRIGGER_INSERT_Locations per la tabella Posizioni .

 CREATE TRIGGER TRIGGER_INSERT_Locations ON Locations FOR INSERT NOT FOR REPLICATION AS BEGIN INSERT INTO LocationHistory SELECT LocationID , getdate() FROM inserted END;

Il passaggio successivo verifica l'istruzione trigger:

 insert into dbo.Locations values(1,'Ha Noi');

Nel trigger precedente, quando si inseriscono i dati delle posizioni, i dati LocationHistory vengono aggiunti in base ai dati delle posizioni.

In questo c'è una nuova parola chiave Replication è una tecnologia di replica e distribuzione dei dati ed è una delle soluzioni di ripristino di emergenza disponibili in SSMS che è molto utile per mantenere una seconda o una copia di backup della stanza degli oggetti (Table, View, Stored Procedure) e Banca dati. Il significato NOT FOR REPLICATION non viene utilizzato per la situazione di replica.

Aggiorna trigger

L'istruzione di attivazione UPDATE copia il record originale nella tabella Eliminata e copia il nuovo record nella tabella inserita quando il record viene aggiornato. Quindi valuta il nuovo record per determinare se il valore soddisfa il vincolo nella tabella.

Se il nuovo valore è valido, il record della tabella inserita verrà copiato nella tabella trigger. Tuttavia, se il nuovo valore non è valido, verrà stampato un messaggio di errore all'utente. Quindi i record originali vengono copiati dalla tabella Eliminata nella tabella trigger.

È possibile creare un trigger UPDATE utilizzando la parola chiave UPDATE nel comando CREATE TRIGGER o ALTER TRIGGER.

Per esempio:

 CREATE TRIGGER TRIGGER_UPDATE_Locations ON Locations FOR UPDATE NOT FOR REPLICATION AS BEGIN INSERT INTO LocationHistory SELECT LocationID, getdate() FROM inserted END; GO update Locations SET LocName = 'Ho Chi Minh' where [LocName] = 'Ha Noi';

Dopo aver eseguito il comando di creazione del trigger precedente e aver aggiornato i dati nella tabella Posizioni, la tabella LocationHistory viene aggiunta con nuovi dati quando i dati della tabella Posizioni vengono aggiornati.

Elimina trigger

Il trigger di eliminazione viene creato per impedire agli utenti di eliminare record specifici dalla tabella

La seguente sequenza di esecuzione si verifica quando un utente tenta di eliminare un record:

• Il record viene eliminato dalla tabella trigger e aggiunto alla tabella Eliminato .
• Verificherà la corrispondenza del vincolo durante l'eliminazione dei dati
• Se è presente un vincolo sul record che impedisce l'eliminazione, il trigger DELETE visualizzerà un messaggio di errore
• I record eliminati archiviati nella tabella Eliminata verranno copiati nuovamente nella tabella trigger.

Per esempio:

 CREATE TRIGGER TRIGGER_DELETE_Locations ON Locations FOR DELETE NOT FOR REPLICATION AS BEGIN INSERT INTO LocationHistory SELECT LocationID, getdate() FROM deleted END; GO DELETE FROM dbo.Locations WHERE LocName = 'Ho Chi minh';

Dopo aver eseguito l'eliminazione, il trigger aggiungerà nuovi record alla tabella LocationsHistory.

DOPO Trigger

Un trigger AFTER viene eseguito dopo il completamento dell'operazione INSERT, UPDATE, DELETE. Una tabella può avere più trigger AFTER definiti per ogni operazione INSERT, UPDATE, DELETE. Se vengono creati più trigger nella stessa tabella, l'utente deve definire il metodo di esecuzione del trigger stesso. Un trigger AFTER viene eseguito al completamento dei vincoli di controllo, quindi il trigger verrà eseguito dopo la creazione delle tabelle Inserito ed Eliminato .

Sintassi:

 CREATE TRIGGER <trigger_name> ON <table_name> [WITH ENCRYPTION] [FOR | AFTER] { [INSERT] [,] [UPDATE] [,] [DELETE] } AS BEGIN <sql_statement> END;

Per esempio:

 CREATE TRIGGER AFTER_INSERT_Locations ON Locations AFTER INSERT AS BEGIN INSERT INTO LocationHistory SELECT LocationID ,getdate() FROM inserted END;

Dopo l'esecuzione, verrà inserito nella tabella LocationHistory dopo aver inserito correttamente le posizioni, la differenza tra il trigger dopo e il trigger attivato è solo nel tempo di esecuzione del trigger.

INVECE DI Trigger

INSTEAD OF verrà eseguito il trigger invece dell'operazione INSERT, UPDATE, DELETE quando utilizzata. Una tabella o una vista può avere un solo trigger INSTEAD OF definito per ogni operazione INSERT , UPDATE , DELETE.

Il trigger INSTEAD OF verrà eseguito prima dell'esecuzione del controllo dei vincoli sulla tabella, questo trigger viene eseguito dopo la creazione delle tabelle inserite ed eliminate .

Per esempio:

 CREATE TRIGGER INSTEAD_OF_DELETE_Locations ON Locations INSTEAD OF DELETE AS BEGIN SELECT 'example instead trigger' as Message END; GO DELETE FROM dbo.Locations WHERE LocName='Ha Noi'

Quando viene eseguita l'istruzione di eliminazione, viene attivato il trigger INSTEAD OF e viene eseguito il blocco all'interno del trigger e l'azione di eliminazione non si verifica.

Ordine di esecuzione dei trigger DML

SQL Server consente all'utente di definire quali trigger AFTER devono essere eseguiti per primi e quali devono essere eseguiti in seguito. Tutti i trigger AFTER quando vengono chiamati tra il primo e l'ultimo trigger non hanno un ordine di esecuzione definito.

Utilizzare la stored procedure sp_settriggerorder per definire l'ordine della porta del trigger DML,

La sintassi per determinare l'ordine di esecuzione del trigger AFTER DML:

 sp_settriggerorder [@triggername=] '[triggerschema.]triggername', [@order=]'value', [@stmmttype=]'statement_type'

Per esempio:

 EXEC sp_settriggerorder @triggername = 'TRIGGER_DELETE_Locations', @order='FIRST', @stmttype='DELETE'

Vedere la definizione di trigger DML

Per visualizzare la definizione del trigger tramite la procedura sp_hepltext .store
Sintassi:

 sp_helptext '<trigger_name>';

Per esempio:

 sp_helptext TRIGGER_DELETE_Locations

Attivazione ALTER DML

Esistono 2 modi per modificare i trigger:

• DROP e RECREATE si attivano di nuovo con un nuovo parametro
• Modificare il trigger con la sintassi ALTER TRIGGER

Sintassi:

 ALTER TRIGGER <trigger_name> ON {<table_name>|<view_name>} [WITH ENCRYPTION] {FOR|ALTER|INSTEAD OF} { [INSERT] [,] [UPDATE] [,] [DELETE] } AS <sql_statement>

Per esempio:

 ALTER TRIGGER TRIGGER_UPDATE_Locations ON Locations WITH ENCRYPTION FOR INSERT AS IF 'Ho Chi Minh' IN (SELECT LocName FROM inserted) BEGIN PRINT 'Location can not be updated' ROLLBACK TRANSACTION END;

Trigger DROP DML

Sintassi:

 DROP TRIGGER <DML_trigger_name> [,...n]

Per esempio:

 DROP TRIGGER TRIGGER_UPDATE_Locations

Trigger DDL

Un trigger DDL (Data Definition Language) esegue una procedura memorizzata quando si verifica un evento DDL come CREATE,ALTER,DROP nel database o nel server. I trigger DDL possono funzionare solo al termine dell'evento DDL.
I trigger DDL possono essere utilizzati per impedire modifiche allo schema del database.
Il trigger DDL può chiamare un evento o visualizzare un messaggio in base al messaggio dello schema. I trigger DDL sono definiti a livello di database oa livello di server.

Sintassi:

 CREATE TRIGGER <trigger_name> ON {ALL SERVER | DATABASE} [WITH ENCRYPTON] {FOR | AFTER} <event_type> } AS <sql_statement>

Per esempio:

 CREATE TRIGGER Secure ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT 'You must disable trigger scure to drop or alter table' ROLLBACK;

Con questo codice, viene generato un trigger DDL per l'istruzione DROP TABLE o ALTER TABLE.

Intervallo di trigger DDL

Il trigger DDDL viene richiamato dall'istruzione sql eseguita nel database o nel server corrente. Ad esempio, viene creato un trigger DDL per l'istruzione CREATE TABLE che viene eseguita sull'evento CREATE TABLE nel database.
Il trigger DDL determina l'esecuzione dell'istruzione CREATE LOGIN sull'evento LOGIN sul server.

L'ambito di un trigger DDL dipende dal fatto che il trigger venga eseguito per un evento del database o per un evento del server, i trigger sono divisi in due tipi come segue:

Trigger DDL con ambito database

Il trigger DDL con ambito database viene chiamato dall'evento di modifica dello schema del database. Questi trigger vengono memorizzati nel database ed eseguiti sull'evento DDL, ad eccezione di quelli associati agli appunti.

Trigger DDL con ambito server

Il trigger DDL con ambito server viene chiamato dall'evento DDL del livello server. Questi trigger sono archiviati nel database master.

Trigger annidato

Entrambi i trigger DDL e DML hanno questo concetto quando un trigger implementa un'azione che avvia un altro trigger. I trigger DDL e DML possono essere nidificati fino a 32 livelli. Supponiamo che se il trigger modifica una tabella su cui è presente un altro trigger, il secondo trigger viene inizializzato, quindi chiama il terzo trigger e così via…

Se i trigger nidificati possono essere eseguiti, i trigger inizieranno in sequenza come un ciclo infinito, se raggiunge l'ultima sfumatura, il trigger terminerà.

I trigger nidificati possono essere utilizzati per gestire funzioni come l'archiviazione o il backup dei record interessati da azioni precedenti.

L'utente può disabilitare i trigger nidificati, configurando l'opzione dei trigger nidificati tramite la stored procedure sp_configure su 0 o OFF. La configurazione predefinita consentirà trigger nidificati.

Per esempio:

 GO CREATE TRIGGER Employee_Deletion ON HumanResources.Employee AFTER DELETE AS BEGIN PRINT 'Deletion will affect EmployeePayHistory table' DELETE FROM EmployeePayHistory WHERE BusinessEntityID IN (SELECT BusinessEntityID FROM deleted) END; GO CREATE TRIGGER Deletion_Comnfirmation ON HumanResources.EmployeePayHistory AFTER DELETE AS BEGIN PRINT 'Employe details successfully deleted from EmployeePayHistory table' END; DELETE FROM HumanResources.EmployeePayHistory WHERE BusinessEntityID=1

UPDATE()

La funzione UPDATE() restituisce un valore booleano che determina se un UPDATE o un INSERT è stato eseguito sulla vista o sulla colonna della tabella.

La funzione UPDATE() può essere utilizzata ovunque all'interno del corpo di un trigger UPDATE o INSERT per verificare se il trigger deve eseguire qualche azione.

Sintassi:

 UPDATE (column);

Per esempio:

 CREATE TRIGGER Accounting ON Production.TransactionHistory AFTER UPDATE AS IF (UPDATE(TransactionID) OR UPDATE(ProductID)) BEGIN RAISEERROR(5009,16,10) END; GO

Elabora più righe (record) in una sessione

Quando un utente scrive codice per un trigger DML, le istruzioni si basano sul trigger per attivare singole istruzioni. Una singola istruzione aggiornerà più record nei dati. Questo è un comportamento comune nei trigger DELETE e UPDATE e spesso le istruzioni influiscono su più record. Il comportamento per il trigger INSERT sarà meno comune poiché l'istruzione di inserimento normalmente aggiunge solo 1 record.

Quando una funzionalità del trigger DML chiama automaticamente e ricalcola un riepilogo dei valori di una tabella e archivia i risultati in un'altra tabella, le considerazioni su più righe sono importanti.

Ad esempio, considera la situazione di archiviazione di un risultato di somma di inserimento di una singola riga:

 USE AdventureWorks2019; GO CREATE TRIGGER PODetails ON Purchasing.PurchaseOrderDetail AFTER INSERT AS UPDATE PurchaseOrderHeader SET SubTotal = Subtotal + LineTotal FROM INSERTED WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID;

Nel codice seguente, il totale parziale viene calcolato e memorizzato per ogni operazione di comando di inserimento riga singola.
Il codice seguente verrà eseguito per situazioni a più righe o a riga singola:

 USE AdventureWorks2019; GO CREATE TRIGGER PODetailMultiple ON Purchasing.PurchaseOrderDetail AFTER INSERT AS UPDATE Purchasing.PurchaseOrderHeader SET Subtotal = SubTotal + (SELECT SUM(LineTotal) FROM inserted WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID) WHERE PurchaseOrderHeader.PurchaseOrderID IN (SELECT PurchaseOrderID FROM inserted);

ACCESSO Trigger

Questi trigger vengono attivati in risposta all'evento LOGON in SQL Server. I trigger di accesso attivano la stored procedure nell'evento LOGON. Questo evento viene rilevato quando viene stabilita una sessione utente con un'istanza di SQL Server. Il trigger di accesso si verifica al termine dell'autenticazione di accesso, ma prima che sia stata effettivamente stabilita una sessione utente. Il trigger di accesso non viene eseguito quando la convalida non riesce.

I trigger di accesso vengono creati a livello del server e sono utili nei seguenti casi:

• Controlla l'attività di accesso
• Controlla le azioni di accesso

Ad esempio, crea una tabella LoginActivity con le colonne LOGONEvent e Logintime create in adventureworks come segue:

 CREATE TABLE LoginActivity (LOGONEvent XML, Logintime datetime)

Crea un trigger in modo che quando l'utente accede, inserirà la cronologia di accesso nella tabella LoginActivity:

 CREATE TRIGGER [track_login] ON ALL SERVER FOR LOGON AS BEGIN INSERT INTO LoginActivity SELECT EVENTDATA(), GETDATE() END;

Nota: fare molta attenzione quando si utilizza LOGON Trigger, potrebbe causare una situazione in cui l'errore logico TRIGGER non può essere registrato, è necessario aprire il DAC per connettersi, quindi testarlo attentamente prima di eseguirlo.

Attiva le prestazioni

In pratica, i trigger non comportano alcun costo, ma si comportano abbastanza bene. Tuttavia, molti problemi di prestazioni possono essere causati dalla logica presente all'interno del trigger. Supponendo che un trigger crei un puntatore e esegua l'iterazione su più righe, l'esecuzione subirà un rallentamento.

Allo stesso modo, considera la situazione in cui il trigger esegue più istruzioni SQL impedendo tabelle separate nelle tabelle inserite ed eliminate. Restituirà di nuovo il risultato e rallenterà la query più che non utilizzare il trigger.

Pertanto, è meglio mantenere la logica del trigger semplice ed evitare di utilizzare i puntatori durante l'esecuzione di istruzioni nelle tabelle e varie attività che riducono le prestazioni del sistema.