SQL Server Trigger Example

Da: Daniel Farina | Aggiornato: 2019-02-26 | Commenti (7) | Correlati: More > Trigger

Problema

Hai già imparato a scrivere query SQL e stored procedure, ma ora vuoi imparare i trigger di SQL Server. Questo suggerimento servirà come punto di partenza e come guida alla creazione dei trigger di SQL Server.

Soluzione

I trigger sono uno degli argomenti più incompresi dalle persone nuove a SQL Server.Forse questo è dovuto al fatto che permettono quasi tutte le stesse funzionalità delle stored procedure, rendendo lo sviluppatore inesperto confuso se creare una stored procedure o un trigger.

Che cos’è un SQL Server Trigger?

Un SQL Server trigger è un pezzo di codice procedurale, come una stored procedure che viene eseguito solo quando accade un dato evento. Ci sono diversi tipi di eventi che possono attivare un trigger. Solo per citarne alcuni, l’inserimento di righe in una tabella, un cambiamento nella struttura di una tabella e anche un utente che entra in un’istanza di SQL Server.

Ci sono tre caratteristiche principali che rendono i trigger diversi dalle stored procedure:

• I trigger non possono essere eseguiti manualmente dall’utente.
• Non c’è la possibilità per i trigger di ricevere parametri.
• Non si può commettere o rollbackare una transazione all’interno di un trigger.

Il fatto che sia impossibile usare parametri sui trigger non è una limitazione a ricevere informazioni dall’evento che scatta. Come vedrete più avanti, ci sono alternative per ottenere informazioni sull’evento che scatta.

Classi di SQL Server Triggers

Ci sono due classi di trigger in SQL Server:

• DDL (Data Definition Language) triggers. Questa classe di trigger si attiva in caso di eventi che cambiano la struttura (come la creazione, la modifica o l’eliminazione di una tabella), o in certi eventi relativi al server come i cambiamenti di sicurezza o gli eventi di aggiornamento delle statistiche.
• DML (Data Modification Language) triggers. Questa è la classe di trigger più usata. In questo caso l’evento di innesco è un’istruzione di modifica dei dati; potrebbe essere un’istruzione di inserimento, aggiornamento o cancellazione su una tabella o una vista.

Inoltre, i trigger DML hanno diversi tipi:

• FOR o AFTER: questi tipi di trigger sono eseguiti dopo la fine dell’istruzione di innesco (un inserimento, un aggiornamento o una cancellazione).
• INSTEAD OF : Contrariamente al tipo FOR (AFTER), l’INSTEAD OF viene eseguito al posto della dichiarazione di attivazione. In altre parole, questo tipo di trigger sostituisce l’istruzione di esecuzione. Questo è molto utile nei casi in cui è necessario avere un’integrità referenziale tra i database.

Qual è l’importanza dei trigger di SQL Server?

Una delle caratteristiche fondamentali dei database relazionali è la coerenza dei dati, il che significa che le informazioni memorizzate nel database devono essere coerenti in ogni momento per ogni sessione e ogni transazione. Il modo in cui i motori di database relazionali come SQL Server implementano questo è far rispettare i vincoli come le chiavi primarie e le chiavi esterne. Ma a volte questo non è sufficiente.

In SQL Server non c’è la possibilità di applicare l’integrità referenziale tra due tabelle usando chiavi esterne se queste tabelle sono in diversi database o diversi server. In tal caso l’unico modo per implementarla è l’uso di trigger.

Come faccio a sapere quali righe sono state aggiornate, inserite o cancellate usando un trigger SQL ServerDML?

Nel caso dei trigger DML, ci sono due tabelle virtuali durante l’esecuzione del trigger che contengono i dati interessati dall’esecuzione del trigger. Queste tabelle si chiamano inserite e cancellate e hanno la stessa struttura della tabella di base. Qualcosa da tenere a mente è che le tabelle inserite e cancellate non sono sempre disponibili insieme (cioè si può avere la tabella inserita, ma non la tabella cancellata o viceversa). Troverete maggiori informazioni su queste tabelle nel seguente suggerimentoComprendere le tabelle inserite e cancellate di SQL Server per i trigger DML.

SQL Server DML Trigger Syntax

Nella prossima sezione di codice, vedrete la sintassi base di CREATE TRIGGER.

CREATE TRIGGER trigger_name ON { Table name or view name } { FOR | AFTER | INSTEAD OF } { , , }

Inoltre, la prossima tabella descrive ciascuno degli argomenti della sintassi CREATE TRIGGERs.

SQL Server Trigger Usage Scenarios

Ci sono due chiari scenari in cui i trigger sono la scelta migliore: auditing e applicazione di regole di business. Usando un trigger, si può tenere traccia dei cambiamenti su una data tabella scrivendo un record di log con informazioni su chi ha fatto il cambiamento e cosa è stato cambiato nella tabella.

Forse si pensa che si possa fare lo stesso nell’applicazione con una stored procedure che gestisce la modifica dei dati come gli inserimenti e gli aggiornamenti. Potete usare una stored procedure, ma in tal caso non sarete in grado di registrare le modifiche effettuate direttamente nel database dall’esterno dell’applicazione.

Lo stesso accade quando volete applicare le regole di business con una stored procedure: se qualcuno modifica i dati della tabella di base dall’esterno dell’applicazione potete avere un problema perché la coerenza dei dati non può essere garantita. Per evitare questo problema, bisogna assicurarsi che la stored procedure sia l’unico modo per accedere alla tabella.

Campione di SQL Server DML Trigger

Supponiamo di avere un database per il dipartimento delle risorse umane. Con l’aiuto di un trigger possiamo mantenere un record di audit in una tabella separata che contiene ogni modifica di record, così come l’utente che ha fatto la modifica e l’ora in cui è avvenuta.

Prima dobbiamo creare la tabella Employees.

CREATE TABLE Employees ( EmployeeID integer NOT NULL IDENTITY(1, 1) , EmployeeName VARCHAR(50) , EmployeeAddress VARCHAR(50) , MonthSalary NUMERIC(10, 2) PRIMARY KEY CLUSTERED (EmployeeID) )GO

Poi dobbiamo creare la tabella EmployeesAudit per conservare i record di audit. Questa tabella ha la stessa struttura della tabella Employees, ma include una colonna AuditId come chiave primaria, ModifiedDate per mantenere la data di modifica, ModifiedBy per sapere chi ha modificato la tabella Employees e infine Operation dove indicheremo l’operazione DML che ha generato il record di audit con una delle tre lettere (I perinsert, U per Update e D per Delete).

CREATE TABLE EmployeesAudit ( AuditID INTEGER NOT NULL IDENTITY(1, 1) , EmployeeID INTEGER , EmployeeName VARCHAR(50) , EmployeeAddress VARCHAR(50) , MonthSalary NUMERIC(10, 2) , ModifiedBy VARCHAR(128) , ModifiedDate DATETIME , Operation CHAR(1) PRIMARY KEY CLUSTERED ( AuditID ) )GO

Per poter testare il trigger di esempio abbiamo bisogno di aggiungere alcuni dati alla tabellaEmployees.

INSERT INTO dbo.Employees ( EmployeeName , EmployeeAddress , MonthSalary )SELECT 'Mark Smith', 'Ocean Dr 1234', 10000UNION ALLSELECT 'Joe Wright', 'Evergreen 1234', 10000UNION ALLSELECT 'John Doe', 'International Dr 1234', 10000UNION ALLSELECT 'Peter Rodriguez', '74 Street 1234', 10000GO

Ora che abbiamo impostato l’ambiente di test, è ora di creare il nostro trigger. Date un’occhiata al codice qui sotto.

CREATE TRIGGER TR_Audit_Employees ON dbo.Employees FOR INSERT, UPDATE, DELETEAS DECLARE @login_name VARCHAR(128) SELECT @login_name = login_name FROM sys.dm_exec_sessions WHERE session_id = @@SPID IF EXISTS ( SELECT 0 FROM Deleted ) BEGIN IF EXISTS ( SELECT 0 FROM Inserted ) BEGIN INSERT INTO dbo.EmployeesAudit ( EmployeeID , EmployeeName , EmployeeAddress , MonthSalary , ModifiedBy , ModifiedDate , Operation ) SELECT D.EmployeeID , D.EmployeeName , D.EmployeeAddress , D.MonthSalary , @login_name , GETDATE() , 'U' FROM Deleted D END ELSE BEGIN INSERT INTO dbo.EmployeesAudit ( EmployeeID , EmployeeName , EmployeeAddress , MonthSalary , ModifiedBy , ModifiedDate , Operation ) SELECT D.EmployeeID , D.EmployeeName , D.EmployeeAddress , D.MonthSalary , @login_name , GETDATE() , 'D' FROM Deleted D END END ELSE BEGIN INSERT INTO dbo.EmployeesAudit ( EmployeeID , EmployeeName , EmployeeAddress , MonthSalary , ModifiedBy , ModifiedDate , Operation ) SELECT I.EmployeeID , I.EmployeeName , I.EmployeeAddress , I.MonthSalary , @login_name , GETDATE() , 'I' FROM Inserted I ENDGO

Fondamentalmente il codice consiste nell’ottenere l’utente che sta modificando l’Employeestable guardando la Dynamic ManagementView di sys.dm_exec_sessions per la sessione con lo SPID corrente. Dopo di che il trigger inserisce un record nella tabella EmployeesAudit per ogni record inserito, aggiornato o cancellato nella tabellaEmployees così come l’ora corrente e l’operazione DML che ha innescato il trigger.

Per provare il trigger, ho creato tre query. Ho messo il codice all’interno di una transazione solo per mantenere l’ordine nel mio ambiente di test, potete ometterlo.

La prima di queste query è un aggiornamento.

BEGIN TRANSACTIONSELECT *FROM dbo.EmployeesWHERE EmployeeID = 1 UPDATE EmployeesSET EmployeeName = 'zzz'WHERE EmployeeID = 1 SELECT *FROM dbo.EmployeesWHERE EmployeeID = 1 SELECT *FROM dbo.EmployeesAuditROLLBACK TRANSACTION

Nella prossima schermata vedrete il record aggiornato nella tabella Employees e il nuovo record in EmployeesAudit che tiene traccia dell’operazione DML sulla tabella Employees.

La seconda query è un inserimento di due righe nella tabella Employees.

BEGIN TRANSACTIONINSERT INTO dbo.Employees ( EmployeeName , EmployeeAddress , MonthSalary ) SELECT 'zz' , 'dsda' , 10000 UNION ALL SELECT 'Markus Rubius' , 'dsda' , 6000SELECT *FROM dbo.EmployeesSELECT *FROM dbo.EmployeesAuditROLLBACK TRANSACTION

Nella prossima schermata vedrete le due righe inserite nella tabella Employeestable e i rispettivi record di audit nella tabella EmployeesAudit.

Finalmente, la terza query è una dichiarazione di cancellazione sulla tabella Employees.

BEGIN TRANSACTIONSELECT *FROM dbo.EmployeesWHERE EmployeeID = 1DELETE FROM dbo.EmployeesWHERE EmployeeID = 1 SELECT *FROM dbo.EmployeesAuditSELECT *FROM dbo.EmployeesWHERE EmployeeID = 1ROLLBACK TRANSACTION

Nella prossima schermata si vedrà la riga cancellata dalla tabella Employees e il suo rispettivo record di audit nella tabella EmployeesAudit.

Passi successivi

• Se hai trovato difficile capire il modo in cui ho catturato quale operazione DML è stata eseguita nella tabella Employees dai un’occhiata a questo suggerimento:Comprendere le tabelle inserite e cancellate di SQL Server per i trigger DML.
• Per un esempio di un trigger INSTEAD OF potete dare un’occhiata a questo tip:Using INSTEAD OF triggers in SQL Server for DML operations.
• Potete trovare maggiori informazioni sul DMV sys.dm_exec_sessions qui:Understanding and Using sys.dm_exec_sessions in SQL Server.
• Per una spiegazione più dettagliata del comando EXECUTE AS dai un’occhiata al prossimo suggerimento:Concedere i permessi con il comando EXECUTE AS in SQL Server.
• Stai sintonizzato con la categoria Suggerimenti su SQL Server Triggers per altri suggerimenti e trucchi.

Ultimo aggiornamento: 2019-02-26

Informazioni sull’autore

Daniel Farina è nato a Buenos Aires, Argentina. Autodidatta, fin dall’infanzia ha mostrato una passione per l’apprendimento.
Vedi tutti i miei consigli

Risorse correlate

• Altri consigli per sviluppatori di database…