Con l’articolo di oggi vogliamo dare una panoramica su degli oggetti particolari che si possono trovare e creare nei database SQL Server denominati TRIGGER, ovvero, citando la guida Microsoft: un tipo speciale di stored procedure che viene invocata automaticamente in risposta a determinati eventi.
Cosa sono i trigger e in cosa differiscono da una normale stored procedure?
Tipologie di trigger
Esistono diverse tipologie di trigger basate su eventi specifici e sul momento in cui vengono eseguiti rispetto ad una determinata operazione, vediamoli nel dettaglio di seguito.
1. Trigger DML (Data Manipulation Language)
- AFTER Trigger. Si attivano dopo che l'operazione DML (INSERT, UPDATE o DELETE) è stata completata con successo
- INSTEAD OF Trigger. Si attivano al posto dell'operazione DML. Invece di eseguire l'operazione originale, viene eseguita la logica definita nel trigger.
2. Trigger DDL (Data Definition Language)
I trigger DDL si attivano in risposta a eventi che modificano la struttura dell’istanza o del database, come comandi CREATE, ALTER, o DROP. Sono utilizzati per controllare o registrare le modifiche apportate alle strutture, come la creazione o modifica di tabelle, viste, stored procedure, utenti, ecc.
3. Trigger di Logon
- Limitare l’accesso a determinati criteri (es. date o orari).
- Implementare controlli avanzati di autenticazione.
4. Trigger di tipo CLR (Common Language Runtime)
- Abilitare l'integrazione CLR in SQL Server.
- Scrivere il codice del trigger in un linguaggio .NET (Vb.Net o C#) e caricarlo come assembly nel database.
Le tabelle virtuali INSERTED e DELETED
INSERTED
- INSERT: La tabella Inserted conterrà le righe che sono state appena inserite.
- UPDATE: La tabella Inserted conterrà le righe con i nuovi valori aggiornati (cioè, il "dopo" dell'aggiornamento).
DELETED
- DELETE: La tabella Deleted conterrà le righe che sono state cancellate.
- UPDATE: La tabella Deleted conterrà le righe con i vecchi valori prima dell'aggiornamento (cioè, il "prima" dell'aggiornamento).
Esempi pratici di implementazione
Di seguito qualche esempio base di implementazione e funzionamento dei trigger.
1. Trigger AFTER INSERT per il logging di nuovi record
- LogID INT IDENTITY(1,1) PRIMARY KEY
- ProductId INT
- ActionDate DATETIME
- Action NVARCHAR(10)
CREATE TRIGGER trg_AfterInsertProduct ON Product AFTER INSERT AS BEGIN SET NOCOUNT ON; INSERT INTO ProductsLog ( ProductId ,ActionDate ,Action ) SELECT ProductID ,GETDATE() ,‘Inserted’ FROM Inserted; END;
2. Trigger AFTER DELETE per registrare cancellazioni
Tenendo valido l’esempio del punto 1 possiamo inserire nella tabella di log anche quando viene eliminato un prodotto gestendo anche un trigger per la cancellazione:
CREATE TRIGGER trg_AfterDeleteOrders ON Product AFTER DELETE AS BEGIN SET NOCOUNT ON; INSERT INTO ProductsLog ( ProductId ,ActionDate ,Action ) SELECT ProductID ,GETDATE() ,‘Deleted’ FROM Deleted; END;
3. Trigger per impedire l'eliminazione di tabelle nel database
In questo esempio, il trigger DDL bloccherà qualsiasi tentativo di eliminare qualsiasi tabella nel database e restituirà un messaggio di errore.
CREATE TRIGGER trg_PreventTableDrop ON DATABASE FOR DROP_TABLE AS BEGIN RAISERROR ( 'Non è permesso eliminare tabelle in questo database.' ,16 ,1 ); ROLLBACK; END;
4. Trigger a livello di istanza per impedire la creazione di un nuovo database
In questo esempio si blocca la creazione di un nuovo database sull’istanza corrente.
CREATE TRIGGER ddl_trig_database ON ALL SERVER FOR CREATE_DATABASE AS RAISERROR ( 'Non è permesso creare database' ,16 ,1 ); ROLLBACK; GO
5. Trigger per limitare l’accesso in base all'orario
Come ultimo esempio vediamo una possibile applicazione per bloccare una login in base all’orario di sistema.
CREATE TRIGGER trg_LimitLoginHours ON ALL SERVER FOR LOGON AS BEGIN DECLARE @CurrentTime TIME = CONVERT(TIME, GETDATE()); DECLARE @CurrentLogin VARCHAR(MAX) = ORIGINAL_LOGIN(); IF @CurrentTime NOT BETWEEN '08:00' AND '18:00' AND @CurrentLogin LIKE ‘user % ’ BEGIN RAISERROR ( 'Accesso consentito solo tra le 8:00 e le 18:00.' ,16 ,1 ); ROLLBACK; END END;
Vantaggi e svantaggi dei trigger
Vantaggi
- Automazione delle operazioni: consentono di eseguire automaticamente azioni in risposta a eventi specifici senza che sia necessario un intervento manuale. Ciò è utile per implementare logiche come aggiornamenti automatici o la creazione di log.
- Garanzia di integrità dei dati: possono essere utilizzati per garantire l'integrità referenziale e la coerenza dei dati. Possono prevenire la cancellazione o l'aggiornamento di dati critici, verificare condizioni complesse, o mantenere relazioni tra diverse tabelle in modo automatico.
- Auditing e tracciabilità: È possibile utilizzare i trigger per registrare automaticamente tutte le modifiche ai dati e strutture del database
- Controlli di sicurezza: I trigger possono implementare controlli di sicurezza personalizzati. Ad esempio, si possono utilizzare per bloccare certe operazioni in base a determinate condizioni o per garantire che solo determinati utenti possano eseguire modifiche specifiche.
- Interventi immediati e automatici: I trigger agiscono immediatamente quando si verifica l'evento per cui sono stati progettati, senza che sia necessario scrivere codice extra in ogni singola query o applicazione che interagisce con il database.
Svantaggi
- Impatto sulle prestazioni: Poiché i trigger vengono eseguiti automaticamente al verificarsi di eventi, possono rallentare le operazioni di INSERT, UPDATE o DELETE, specialmente se contengono logiche complesse o se si attivano su tabelle con un grande volume di dati. L'aggiunta di troppi trigger o trigger con codice inefficiente può portare a un calo significativo delle prestazioni.
- Difficoltà di debug e manutenzione: I trigger sono eseguiti in modo implicito, il che può rendere difficile individuare i problemi o capire esattamente quale codice è stato eseguito e quando. Questo può rendere complesso il debugging, soprattutto in sistemi di grandi dimensioni con molti trigger in esecuzione su diverse tabelle.
- Comportamento non visibile all'applicazione: Poiché i trigger vengono eseguiti "dietro le quinte", un'applicazione che interagisce con il database potrebbe non essere consapevole che certe operazioni sono state eseguite automaticamente (ad esempio, un aggiornamento o una cancellazione), il che può portare a confusione o risultati inattesi.
- Possibili problemi di ricorsione: Se non gestiti correttamente, i trigger possono portare a situazioni di ricorsione involontaria. Ad esempio, un trigger AFTER INSERT potrebbe eseguire un'operazione che attiva di nuovo lo stesso trigger o un altro trigger collegato, creando una catena di esecuzioni non voluta che può portare a loop infiniti o a problemi di prestazioni.
- Dipendenza eccessiva dai trigger: Un uso eccessivo dei trigger per implementare logiche di business può rendere il database troppo complesso e "rigido". Alcune logiche di business dovrebbero essere gestite a livello di applicazione anziché direttamente nel database, per garantire una maggiore flessibilità e un controllo più esplicito.
- Influenza sulle transazioni: I trigger vengono eseguiti all'interno della transazione che li attiva, quindi se un trigger fallisse, l'intera transazione potrebbe essere annullata. Questo può portare a rollback imprevisti di operazioni legittime, se non si gestiscono correttamente gli errori all'interno del trigger.
Best Practices per l'Utilizzo dei Trigger
- Mantenere i trigger semplici e veloci: non inserire logiche complesse o operazioni che richiedono molto tempo all'interno dei trigger. Operazioni lunghe possono rallentare le transazioni DML che li attivano.
- Gestione degli errori ovvero utilizzare TRY...CATCH nei trigger: poiché un errore nel trigger può causare il rollback dell'intera transazione che lo ha invocato, è importante gestire gli errori all'interno del trigger stesso. Usare blocchi TRY...CATCH per catturare e gestire eventuali errori senza interrompere l'operazione principale è ottimale.
- Limitare l'ambito del trigger: Controllare che il trigger agisca solo su dati rilevanti ovvero attivare condizioni per limitare l'esecuzione del trigger solo quando è necessario. Ad esempio, verifica se i dati sono stati effettivamente modificati per colonne interessate prima di eseguire il codice del trigger.
- Gestione delle tabelle virtuali (Inserted e Deleted): utilizzare correttamente le tabelle Inserted e Deleted. Assicurati di utilizzarle correttamente per accedere ai dati modificati, specialmente nei trigger AFTER e INSTEAD OF.
- Evitare la ricorsione involontaria: disabilita la ricorsione nei trigger, se non necessaria. SQL Server consente la ricorsione nei trigger, ma può portare a loop infiniti o a esecuzioni ripetute non volute. Se non hai bisogno di ricorsione, puoi disabilitarla con il comand ALTER DATABASE <<nome_database>> SET RECURSIVE_TRIGGERS OFF. Evitare l'auto-invocazione: Se un trigger modifica la stessa tabella su cui è definito, potrebbe innescare nuovamente il trigger stesso. Va gestito questo comportamento con attenzione per evitare esecuzioni indesiderate.
- Ottimizzazione delle prestazioni. Riduci l'impatto sulle prestazioni: i trigger possono rallentare le operazioni DML; quindi, è importante ottimizzare la logica al loro interno per ridurre al minimo il loro impatto. Ad esempio, evita query costose o join complessi all'interno di un trigger. Operazioni di I/O pesanti come scritture su file o inserimenti in tabelle di log molto grandi dovrebbero essere evitate.
- Test approfonditi e monitoraggio. Testare accuratamente i trigger: poiché si attivano automaticamente, è fondamentale testarli con cura in ambiente di sviluppo per garantire che funzionino come previsto e che non abbiano impatti indesiderati sulle prestazioni o sui dati. Monitorare le prestazioni: utilizzare se possibile strumenti di monitoraggio per verificare il comportamento dei trigger in produzione e assicurati che non causino colli di bottiglia o rallentamenti imprevisti.
- Documentare il comportamento dei trigger. Documentare la logica dei trigger: spesso i trigger vengono dimenticati o ignorati poiché agiscono in background. Documentare chiaramente perché e quando un trigger è stato creato, e quale comportamento è previsto è basilare per evitare di perdere ore alla ricerca di risultati disattesi.