I trigger in SQL Server

Datamaze
05.02.25 09:43 AM Comment(s)

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?

In SQL Server, i trigger sono oggetti dell’stanza o del database che si attivano automaticamente in risposta a determinati eventi. Un trigger è fondamentalmente un blocco di codice che viene eseguito quando si verificano eventi, per esempio, come CREATE, DROP, INSERT, UPDATE o DELETE.               

Possono quindi essere visti come delle stored procedure particolari che hanno la caratteristica di essere attivate automaticamente e solamente dall’evento che le scatena. 

Rispetto alla stored procedure hanno dei limiti, per esempio, non possono gestire transazioni al proprio interno (indipendenti da quella in corso) e non possono tornare un valore a fronte della loro esecuzione. 

In altre parole, un trigger esprime il massimo delle sue potenzialità quando a fronte di una modifica sul e sui dati del database è necessario eseguire un’operazione automatizzata e ripetitiva.

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)

I trigger DML si attivano in risposta a modifiche sui dati del database tramite operazioni DML come INSERT, UPDATE o DELETE. Si dividono in due categorie principali: 
  • 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. 

NB: È possibile gestire più combinazioni di operazioni DML ovvero possiamo eseguire lo stesso blocco di codice se vengono eseguite sia INSERT che UPDATE e DELETE con lo stesso 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

I Logon Trigger si attivano in risposta a tentativi di accesso al server SQL. Vengono utilizzati solitamente per controllare o registrare i tentativi di accesso degli utenti e per implementare politiche di sicurezza personalizzate. 

Si attivano prima che la sessione utente venga stabilita e possono essere utilizzati per: 
  • Limitare l’accesso a determinati criteri (es. date o orari).
  • Implementare controlli avanzati di autenticazione. 

4. Trigger di tipo CLR (Common Language Runtime)

Oltre ai trigger scritti in T-SQL, SQL Server supporta l'uso di CLR Trigger, ovvero trigger scritti in linguaggi .NET come C# o VB.NET. Questi trigger permettono di eseguire logiche più complesse rispetto ai trigger tradizionali grazie al  Runtime .NET. 

In questi casi per poter applicare un trigger CLR è necessario: 
  • 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

Come abbiamo detto un trigger permette di “reagire” a determinati eventi e modifica dei dati, a questo punto è bene fare chiarezza sul dove sono disponibili le modifiche: durante l’implementazione dei trigger si hanno disposizione implicitamente due tabelle virtuali INSERTED e DELETED. 

Come suggerisce anche il nome non sono tabelle fisiche nel database, ma rappresentano in modo temporaneo i dati coinvolti nelle operazioni DML durante l'esecuzione di un trigger. 

INSERTED

La tabella Inserted contiene i nuovi dati che vengono inseriti o aggiornati durante l'esecuzione di un'operazione DML. 
  • 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

La tabella Deleted contiene i dati che sono stati cancellati o sostituiti durante l'operazione DML. 
  • 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). 

Il contenuto delle tabelle, quindi, varia in base all’operazione DML e al contesto, per esempio durante un trigger che gestisce solamente eventuali INSERT la tabella DELETED sarà vuota in quanto nessun dato viene eliminato o sovrascritto.
 

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

Immaginiamo lo scenario di voler registrare un log dell’inserimento di ogni nuovo prodotto registrato sulla tabella “Production.Product” del database demo Microsoft AdventureWorks.

Immaginando di aver creato la tabella ProductsLog come a scopo di tener traccia delle modifiche: 
  • LogID INT IDENTITY(1,1) PRIMARY KEY 
  • ProductId INT 
  • ActionDate DATETIME 
  • Action NVARCHAR(10) 

Il codice del trigger potrebbe essere: 
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;
“ON Product AFTER INSERT”: indica che l’innesco vale per la tabella “Product” a fronte di una INSERT avvenuto con successo (AFTER INSERT). 

L’informazione sul ProductId del prodotto appena inserito viene recuperata dalla tabella virtuale INSERTED mentre la data viene calcolato dal sistema con la funzione GETDATE(). Il trigger gestisce solamente inserimenti (AFTER INSERT) per cui l’azione registrata può essere tranquillamente la stringa constante “Inserted”.

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;
In questo caso si nota come l’evento scatenante deve essere la cancellazione avvenuta (AFTER DELETE) e per avere l’informazione su quale record è interessato si utilizza la tabella virtuale DELETED. 
Come per l’esempio 1 ad ogni cancellazione di un record corrisponderà la registrazione di una riga di log. 

NB: è possibile ottimizzare i trigger, come detto, gestendo più eventi contemporaneamente e inserendo qualche controllo per determinare la tipa di modifica.

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;
“ON DATABASE” indica che il trigger valido per l’intera base dati. 
“FOR DROP_TABLE” indica che l’innesco è subito prima del DROP di una qualsiasi tabella 
Con RAISERROR viene generato un errore e l’istruzione di DROP viene annullata.

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


“ON ALL SERVER” indica che il trigger valido per l’intera istanza. 
“FOR CREATE DATBASE” indica che l’innesco è alla creazione di un nuovo database.
In maniera analoga all’esempio tre l’innesco questa volta è la creazione di un qualsiasi database.

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;
“ON ALL SERVER” indica che il trigger valido per l’intera istanza. 
“FOR LOGON” indica che l’evento scatenante è una qualsiasi login. 
Se la condizione if viene verificata (ora di sistema non compresa tra le 08 e le 18 e l’utenza deve iniziare per user viene annullato il processo di login e lanciato l’errore.

Vantaggi e svantaggi dei trigger

Vantaggi

  1. 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. 
  2. 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. 
  3. Auditing e tracciabilità: È possibile utilizzare i trigger per registrare automaticamente tutte le modifiche ai dati e strutture del database 
  4. 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. 
  5. 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

  1. 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. 
  2. 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. 
  3. 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. 
  4. 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. 
  5. 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. 
  6. 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

  1. 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. 
  2. 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. 
  3. 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. 
  4. 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. 
  5.  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. 
  6. 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. 
  7. 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. 
  8. 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.

Conclusioni

 trigger rappresentano uno strumento potente e versatile in SQL Server per automatizzare risposte a cambiamenti nei dati e nella struttura del database, migliorando la gestione e il controllo di dati e operazioni. 

Tuttavia, è fondamentale implementare i trigger l'uso eccessivo o inappropriato può influire negativamente sulle prestazioni del database e complicare la manutenzione del codice. Scegliere quando e come usarli, osservando le best practice, permette di sfruttarne appieno il potenziale, garantendo un controllo efficace delle operazioni senza compromettere l'efficienza del sistema.