I Trigger in SQL

I trigger sono degli oggetti di SQL Sever molto simili alle stored procedures, una sorta di procedura particolare che si attiva automaticamente dopo un determinato evento. Gli eventi per i quali si attiva un trigger sono l’esecuzione di una istruzione INSERT / UPDATE / DELETE su una tabella di SQL Server. Il trigger viene ancorato ad una tabella e qualora si verifichi un evento tra quelli descritti prima si attiva eseguendo il codice T-SQL contenuto al suo interno, in questa parte è il trigger è del tutto simile ad una stored procedure.

I trigger sono utilizzati per diversi scopi nella progettazione di un database, e principalmente:

1. per mantenere l’integrità referenziale tra le varie tabelle
2. per mantenere l’integrità dei dati della singola tabella
3. per monitorare i campi di una tabella ed eventualmente generare eventi ad hoc
4. per creare tabelle di auditing per i record che che vengono modificati o eliminati

I trigger possono essere attivati solo su tabelle e non su viste (questo fino alla versione 7.0 di SQL Server).

Cosa sono i trigger in SQL

Concentriamoci ora su come si scrive e su come lavora un trigger, più sotto troverete anche la sintassi dettagliata e precisa su come crearli, eliminarli e modificarli. Un semplice esempio di trigger potrebbe essere questo

CREATE TRIGGER TR_DEL_Employees ON Employees FOR DELETE /* , INSERT, UPDATE più azioni contemporaneamente */ AS INSERT CrologiaImpiegati SELECT EmployeeID, FirstName, LastName, ‘Eliminato’ AS Azione FROM deleted

Create trigger è l’istruzione che fisicamente crea il trigger TR_DEL_Employees, la parola chiave ON invece ci dice invece la tabella sulla quale viene ancorato mentre la parola chiave FOR indica a quali eventi viene associato. In questo caso il trigger si attiverà per ogni DELETE riguardante la tabella Employees. Possiamo anche specificare più operazioni per cui attivare il triggere oltre a DELETE anche INSERT ed UPDATE. Quando attivato un trigger lavora su due tabelle particolari chiamate inserted e deleted. Difatti abbiamo visto che la SELECT inclusa nel trigger TR_DEL_Employees pesca le informazioni necessarie dalla tabella deleted. E’ facile intuire che nel caso di una operazione di DELETE la tabella deleted conterrà le righe che sono state appena eliminate al contrario con una INSERT la tabella inserted conterrà le righe appena inserite, ma con una UPDATE? In questa caso entrambe le tabelle contengono valori, perchè la deleted conterrà i dati prima della modifica (le vecchie righe) mentre la inserted conterrà i dati dopo la modifica (le nuove righe).

Come funziona il trigger in SQL

Per capire meglio il funzionamento vediamo un esempio:

Use Northwind GO CREATE TRIGGER TR_UPD_Employees ON Employees FOR UPDATE AS DECLARE @LastName NVARCHAR(20) SELECT @LastName = LastName FROM deleted PRINT ‘Prima: ‘ + @LastName SELECT @LastName = LastName FROM inserted PRINT ‘Dopo: ‘ + @LastName GO BEGIN TRAN SET NOCOUNT ON UPDATE dbo.Employees SET LastName = ‘Rossi’ WHERE EmployeeID = 1 ROLLBACK /* Cancello la modifica appena fatta */

Il risultato prodotto nella finestra in basso del QA dovrebbe essere: Prima: Davolio Dopo: Rossi

Il contenuto in termini di numero di righe delle tabelle deleted e inserted ovviamente varierà in funzione del numero di righe che sono coinvolte nelle operazioni di aggiornamento dei dati

Use Northwind GO /* Cambio il contenuto del trigger precedente */ ALTER TRIGGER TR_UPD_Employees ON Employees FOR UPDATE AS DECLARE @conta INT SELECT @conta = COUNT(*) FROM deleted PRINT ‘Righe in deleted: ‘ + CAST(@conta AS VARCHAR) SELECT @conta = COUNT(*) FROM inserted PRINT ‘Righe in inserted: ‘ + CAST(@conta AS VARCHAR) GO SET NOCOUNT ON UPDATE dbo.Employees SET LastName = LastName

Il risultato prodotto nella finestra in basso del QA dovrebbe essere: Righe in deleted: 9 Righe in inserted: 9

Inoltre dobbiamo ricordare che il contenuto delle colonne con un tipo dato ntext, text ed image non vengono conderati dai triggers perché le tabelle inserted e deleted non supportano colonne con simili tipi di dato. Per poter gestire queste colonne ci dobbiamo affidare alle stored procedure!

Nota Bene: Possiamo ancorare più triggers ad una tabella, ma ricordiamoci che non possiamo controllare l’ordine nel quale questi triggers verranno eseguiti, per comodità pensiamo che i trigger si attivino contemporaneamente.

Triggers e transazioni

Il codice TSQL che includiamo all’interno di un trigger è implicitamente inserito nel contesto di una transazione, quindi utilizzando l’istruzione ROLLABACK TRAN all’interno del trigger le modifiche apportate ai dati contenuti nella tabella verrano rifiutate.

Se la tabella su cui è posto il trigger è coinvolta all’interno di una transazione di cui fanno parte altre tabelle il rollback della transazione allinterno del codice del trigger porterà anche al fallimento della transazione principale in cui sono coinvolte tutte le tabelle.

Un esempio: supponiamo di avere due tabelle, T1, T2 e di porre sulla tabella T2 un trigger con il compito di impedire l’inserimento dei caratteri ‘–‘ nella colonna valore.

Use tempdb GO CREATE TRIGGER TR_UPD_test ON t2 FOR INSERT, UPDATE AS IF EXISTS(SELECT 1 FROM inserted WHERE valore=’–‘) BEGIN ROLLBACK TRAN /* quella implicita del trigger */ PRINT ‘Errore il valore — non è permesso!!!’ END GO

Creiamo ora una transazione che conivolga diverse operazioni di aggiormento su entrambe le tabelle T1 e T2.

BEGIN TRAN /* Transazione principale */ UPDATE T1 SET valore = ‘do’ WHERE pkid = 1 UPDATE T1 SET valore = ‘re’ WHERE pkid = 1 UPDATE T2 SET valore = ‘–‘ WHERE pkid = 1 /* Errore tutta la transazione principale fallisce perché il carattere — non può essere inserito */ UPDATE T2 SET valore = ‘tu’ WHERE pkid = 2 UPDATE T2 SET valore = ‘su’ WHERE pkid = 3 IF @@ERROR = 0 COMMIT TRAN ELSE ROLLBACK TRAN GO

Nel momento in cui tentiamo di modificare la colonna valore della tabella T2 con il carattere ‘–‘ tutte le operazione di update sulle tabelle che fanno parte della transazione principale verranno rifiutate in seguito all’esecuzione dell’istruzione ROLLBACK TRAN all’interno del trigger TR_UPD_test.

Quando devono essere utilizzati

Utilizzare i triggers per risolvere problemi di progettazione è molto comodo, ma dobbiamo porre attenzione perché un trigger aggiunge un carico di extra-lavoro a SQL Server. In effetti tramite un trigger possiamo attivare operazioni anche molto complesse (cancellazioni a catena, sfruttamento di cursori, invocazione di stored procedure locali o remote, ecc… ) per cui dobbiamo capire fino a che punto il suo utilizzo è ritenuto indispensabile. Generalmente per i punti indicati prima i trigger sono un male necessario,ma a volte possiamo farne a meno optando per soluzioni come default, vincoli, colonne calcolate, jobs per schedulare azioni ricorsive, stored procedure per gestire operazioni di inserimento, modifica e cancellazione con conseguente gestione degli eventi associati, ecc… Inoltre è buona regola tenere conto del numero di righe che vengono coinvolte dall’azione del trigger, finchè si lavora con tabelle con poche centinaia di righe l’uso di trigger non sconvolge le prestazioni del sistema, ma se pensiamo a tabelle con milioni di righe pensiamoci bene prima di piazzarci sopra dei triggers!

Annidamento e recursività dei trigger

I triggers annidati sono triggers che si attivano in funzione di azioni compiute da altri triggers. Esempio: supponiamo di cancellare una riga dalla tabella T1, il trigger su questa tabella quando è attivato elimina delle righe dalla tabella T2. Ma la tabella T2 contiene a sua volta un trigger che proprio in funzione della attivazione del trigger precedente si attiva compiendo altre eliminazione su una terza tabella T3. Questo è un trigger annidato. La loro utilità maggiore si trova nell’implementazione dell’integrità referenziale (RI) in SQL Server 7.0. (SQL Server 2000 supporta invece l’integrità differenziale a livello dichiarativo detta DRI) Per vedere se SQL Server supporta i triggers annidati possiamo usare la procedura di sistema sp_configure

EXEC sp_configure ‘nested triggers’

Dall’output prodotto andiamo a vedere il valore della colonna run_value, se 0 i triggers annidati non sono attualmente supportati se invece è 1 si. Per cambiare questa impostazione è sufficiente lanciare sp_configure nel seguente modo

EXEC sp_configure ‘nested triggers’, 1 RECONFIGURE

per attivare l’opzione oppure

EXEC sp_configure ‘nested triggers’, 0 RECONFIGURE

per disattivarla, I trigger annidati sono attivi per default.

I triggers recursivi sono un caso particolari di triggers annidati. A differenza dei precedenti il supporto per questi triggers viene attivato a livello del singolo di database. Ve ne sono di due tipi diretti e indiretti: Diretti: è un trigger posto su una tabella il quale allo scatenarsi dell’evento per il quale è stato creato (INSERT, UPDATE o DELETE) richiama se stesso. Indiretti: è un trigger che attiva un trigger in un altra tabella il quale alla fine del suo compito attiva un trigger annidato in un’altra tabella ancora il quale riattiva ancora il primo trigger.

I triggers recursivi sono piuttosto complessi da articolari e possono portarci a facili distorsioni ed errori nella loro progettazione, per questo motivi sono disabilitati per default da SQL Server. La procedura di sistema che porta alla loro attivazione/disattivazione è sp_dboption

EXEC sp_dboption ‘<nome del db>’, ‘recursive triggers’

per vedere lo stato corrente dell’opzione invece per attivarli dobbiamo settate l’opzione

EXEC sp_dboption ‘sviluppo’, ‘recursive triggers’, ‘true’

per disattivarli invece

EXEC sp_dboption ‘sviluppo’, ‘recursive triggers’, ‘true’

Creare eliminare e modificare i trigger AFTER

Ecco la sintassi completa necessaria per la creazione di un trigger:

CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] { { { FOR | AFTER } { [ INSERT ] [ , ] [ UPDATE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS [ { IF UPDATE ( column ) [ { AND | OR } UPDATE ( column ) ] [ …n ] IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) { comparison_operator } column_bitmask [ …n ] } ] sql_statement [ …n ] } }

E’ possibile definire più trigger after su una tabella ed un trigger a sua volta può performare varie azioni sui dati modificati compresa la possibilità di richiamare stored procedure. L’eliminazione di un trigger può avvenire in due modi:

• eliminando la tabella a cui è associato
• attraverso l’pposita istruzione DROP TRIGGER

Ecco la sintassi

DROP TRIGGER {trigger_name} [,..n]

Come possiamo vedere in base alla notazione ,..n possiamo elencare un certo numero di trigger da eliminare. Ogni volta che eliminiamo un trigger SQL Server cancella le tracce della sua esistenza dalle tabelle di sitema sysobjects e syscomments.

Con il codice sottostante eliminiamo un trigger in funzione della sua esistenza o meno all’interno delle tabelle di sistema

USE pubs GO IF EXISTS (SELECT name FROM sysobjects — tabella di sistema WHERE name = ‘TR_test’ AND type = ‘TR’) DROP TRIGGER TR_test GO

Per cambiare il contenuto di un trigger possiamo usare l’istruzione ALTER TRIGGER di cui possiamo esaminare la sintassi qui sotto:

ALTER TRIGGER trigger_name ON ( table | view ) [ WITH ENCRYPTION ] { { ( FOR | AFTER | INSTEAD OF ) { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] } [ NOT FOR REPLICATION ] AS sql_statement [ …n ] } | { ( FOR | AFTER | INSTEAD OF ) { [ INSERT ] [ , ] [ UPDATE ] } [ NOT FOR REPLICATION ] AS { IF UPDATE ( column ) [ { AND | OR } UPDATE ( column ) ] [ …n ] | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) { comparison_operator } column_bitmask [ …n ] } sql_statement [ …n ] } }

Nella prossima puntata parleremo dei cursori lato server e delle loro potenzialità nella manipolazione dei dati all’interno delle tabelle.