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:
- per mantenere l'integrità referenziale tra le varie tabelle
- per mantenere l'integrità dei dati della singola tabella
- per monitorare i campi di una tabella ed eventualmente generare eventi ad hoc
- 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.