Trigger nidificati o ricorsivi in SQL Server 2019

Trigger nidificati o ricorsivi in SQL Server sono azioni che vengono eseguite automaticamente quando viene eseguita una determinata operazione su una tabella di un database, ad esempio INSERT, DROP, UPDATE e così via.

Vengono eseguiti come risultato di operazioni DML (Data Manipulation Language), ad esempio operazioni INSERT, UPDATE, DELETE o DDL (Data Definition Language) come CREATE, ALTER, DROP.

I trigger nidificati o ricorsivi in SQL Server possono essere ampiamente classificati in due tipi: trigger AFTER e trigger INSTEAD OF. I trigger AFTER vengono eseguiti dopo l’esecuzione di un’operazione DML o DDL. I trigger INSTEAD OF vengono eseguiti al posto di un’operazione DML o DDL.

Oltre ad essere attivati dalle operazioni DML e DDL, i trigger in SQL Server possono essere attivati anche da altri trigger. Questo trigger di tipo è denominato trigger nidificato in SQL o trigger ricorsivo.

In questo articolo verrà illustrato il funzionamento dei trigger nidificati o ricorsivi in SQL Server.

Trigger nidificati o ricorsivi: Case Study

Prima di guardare un esempio di trigger nidificati o ricorsivi, creiamo alcuni dati fittizi per avere un case study da analizzare. Eseguiamo lo script seguente:

CREATE DATABASE Showroom
 
GO
 
Use Showroom
CREATE TABLE Car  
(  
   CarId int identity(1,1) primary key,  
   Name varchar(100),  
   Make varchar(100),  
   Model int ,  
   Price int ,  
   Type varchar(20)  
)  
 
insert into Car( Name, Make,  Model , Price, Type)
VALUES ('Corrolla','Toyota',2015, 20000,'Sedan'),
('Civic','Honda',2018, 25000,'Sedan'),
('Passo','Toyota',2012, 18000,'Hatchback'),
('Land Cruiser','Toyota',2017, 40000,'SUV'),
('Corolla','Toyota',2011, 17000,'Sedan')
 
 
CREATE TABLE CarLog  
(  
   LogId int identity(1,1) primary key,
   CarId int , 
   CarName varchar(100),  
)

Nello script creiamo un database chiamato Showroom con due tabelle: Car e CarLog.

La tabella Car ha cinque attributi: CarId, Name, Make, Model, Price e Type.

Successivamente, abbiamo aggiunto 12 record fittizi nella tabella Car.

La tabella CarLog ha tre colonne: LogId, CarId e CarName.

Esempio di Trigger nidificati o ricorsivi

Supponiamo di voler garantire che nessuno possa immettere dati direttamente nella tabella CarLog. Piuttosto, vogliamo essere sicuri che quando i dati vengono immessi nella tabella Car, un sottoinsieme di tali dati viene immesso nella tabella CarLog.

Per fare questo, dobbiamo scrivere due trigger. Il primo trigger verrà creato sulla tabella CarLog e impedirà l’inserimento diretto dei dati nella tabella. Il secondo trigger verrà scritto sulla tabella Car e inserirà i dati nella tabella CarLog dopo aver inserito i dati nella tabella Car.

Scriviamo innanzitutto un trigger nidificato in SQL che impedisce l’inserimento di dati nella tabella CarLog.

Il tipo di trigger sarà INSTEAD OF perché invece di inserire dati nella tabella vogliamo che il trigger mostrerà un messaggio di errore all’utente che l’inserimento diretto non è possibile.

Eseguiamo lo script per la tabella CarLOG:

CREATE TRIGGER [dbo].[CarLOG_INSERT]
       ON [dbo].[CarLog]
INSTEAD OF INSERT
AS
BEGIN
PRINT('DATA CANNOT BE INSERTED DIRECTLY IN CarLog TABLE')
      
END

Nello script viene creato un trigger denominato “CarLog_INSERT” che è un trigger di tipo INSTEAD OF. Il trigger viene eseguito ogni volta che qualcuno tenta di inserire direttamente i record nella tabella CarLog. Il trigger visualizza semplicemente un messaggio all’utente che l’inserimento diretto non è possibile.

Proviamo ora a inserire un record nella tabella CarLog e vediamo se il nostro trigger funziona davvero.

INSERT INTO CarLog(  CarId , CarName)
VALUES (2, 'Civic')

Nell’output verrà visualizzato il messaggio seguente:

Il trigger è stato eseguito e invece di inserire un record nella tabella CarLog, ha visualizzato il messaggio che l’inserimento diretto non è possibile.

Proviamo a selezionare tutti i record della tabella CarLog per verificare che nella tabella CarLog non sia stato inserito alcun record. Eseguire lo script seguente:

SELECT * FROM CarLog

Nell’output, vedrai che la tabella CarLog è vuota.

Ora, creiamo il nostro secondo Trigger sulla tabella Car. Questo verrà eseguito dopo che alcuni record sono stati inseriti nella tabella Car.

Eseguiamo lo script seguente:

CREATE TRIGGER [dbo].[CAR_INSERT]
       ON [dbo].[Car]
AFTER INSERT
AS
BEGIN
       SET NOCOUNT ON;
 
       DECLARE @car_id INT, @car_name VARCHAR(50)
 
       SELECT @car_id = INSERTED.CarId,  @car_name = INSERTED.name       
       FROM INSERTED
 
  
 
       INSERT INTO CarLog
       VALUES(@car_id, @car_name)
END

Il Trigger Car_INSERT è di tipo AFTER INSERT e inserisce i record nella tabella CarLog dopo aver inserito i record nella tabella Car.

Ora, proviamo a testare il Trigger Car_INSERT inserendo dei record nella tabella Car:

insert into Car( Name, Make,  Model , Price, Type)
VALUES ('Mustang','Ford',2014, 25000,'Sedan')

Quando si esegue lo script precedente, verrà visualizzato di nuovo il seguente output del messaggio:

Vediamo se i nostri dati sono stati inseriti o meno nella tabella Car e CarLog. Prima dobbiamo selezionare i record della tabella Car.

SELECT * FROM Car

L’output è simile al seguente:

dove è possibile vedere il record appena inserito in cui il nome dell’auto è “Mustang” e CarId = 6.

Ora vediamo se il nuovo record è stato inserito nella tabella CarLog. Eseguire lo script seguente:

SELECT * FROM CarLog

Ciò significa che il record è stato inserito nella tabella Car, quindi è stato eseguito il trigger nidificato Car_INSERT in SQL Server che ha tentato di inserire i dati nella tabella CarLog. Tuttavia, quando il trigger Car_INSERT ha tentato di inserire dati nella tabella CarLog, viene eseguito anche il trigger CarLog_INSERT annidato che impediva l’inserimento dei dati nella tabella CarLog. Questo mostra come un trigger può essere utilizzato per creare un altro trigger da eseguire.

PS: in alcuni scenari (non proprio il case study indicato) può accadere che venga restituito come errore:

Msg 217, Level 16, State 1, Procedure SecondTrigger, Line 5 [Batch Start Line 20]
Il livello massimo di stored procedure, funzione, trigger o nidificazione della visualizzazione è stato superato (limite 32)

Questo indica che il trigger richiama un altro trigger N volte creando un loop infinito. Ogni volta che un Trigger richiama un’altro Trigger, il livello di annidamento viene incrementato. Quando viene superato il massimo di 32, la transazione viene terminata in automatica da SQL Server. In basso la soluzione all’errore.

Tornando al nostro caso d’uso. Vogliamo impedire l’inserimento diretto dei dati nella tabella CarLog ma nello stesso tempo vogliamo che i dati siano inseriti tramite il Trigger Car_INSERT. Tuttavia, attualmente il trigger CarLog_INSERT impedisce sia l’inserimento diretto quindi è necessario aggiornare il trigger CarLog_INSERT in modo che quando qualcuno tenta di inserire direttamente i dati nella tabella CarLog, l’insert viene impedito, ma quando l’inserimento viene eseguito tramite il trigger Car_INSERT, è consentito.

Trigger nidificato o ricorsivo: utilizziamo TRIGGER_NESTLEVEL

Prima di aggiornare il trigger, dobbiamo sapere che ad ogni trigger viene assegnato un valore intero chiamato @@NESTLEVEL a seconda dell’origine dell’esecuzione del trigger, se il trigger viene eseguito direttamente, il valore per il @@NESTLEVEL per quel trigger è impostato su 1.

Tuttavia, se un trigger viene attivato da un altro trigger, il @@NESTLEVEL viene impostato su 2. Allo stesso modo, se il trigger viene eseguito come risultato di un altro trigger che viene eseguito come risultato di un altro trigger, il @@NESTLEVEL del trigger più interno verrà impostato su 3. Il numero massimo di trigger nidificati consentiti da SQL Server è 32.

Ora che comprendiamo il valore @@NESTLEVEL, aggiorneremo il trigger CarLog_INSERT in modo che quando ha un valore @@NESTLEVEL pari a 1 (inserimento diretto), il record non verrà inserito nella tabella CarLog, ma se il valore @@NESTLEVEL non è uguale a 1 (inserimento tramite un altro trigger con un @@NESTLEVEL di 2), il record verrà inserito. In questo caso utilizziamo la funzione TRIGGER_NESTLEVEL.

Lo script seguente elimina il trigger CarLog_INSERT operazione in SQL Server:

DROP TRIGGER [dbo].[CarLOG_INSERT]

E il seguente script crea la versione aggiornata del trigger CarLog_INSERT che abbiamo discusso sopra:

CREATE TRIGGER [dbo].[CarLOG_INSERT] ON [dbo].[CarLog]
INSTEAD OF INSERT
AS
BEGIN
  IF ( (SELECT trigger_nestlevel() ) = 1 ) 
    PRINT('DATA CANNOT BE INSERTED DIRECTLY IN CarLog TABLE')
--oppure IF TRIGGER_NESTLEVEL() = 1
  ELSE
    BEGIN
       DECLARE @car_id INT, @car_name VARCHAR(50)
 
       SELECT @car_id = INSERTED.CarId,  @car_name = INSERTED.CarName      
       FROM INSERTED
       INSERT INTO CarLog
       VALUES(@car_id, @car_name)
    END
    
END

PS. Nel caso riscontraste l’errore precedentemente indicato “È stato superato il numero massimo di livelli di nidificazione consentito (32) per stored procedure, funzioni, trigger o viste” è sufficiente aggiungere al vostro Trigger il seguente comando:

IF ( ( SELECT TRIGGER_NESTLEVEL ( ( SELECT object_id FROM sys.triggers  
WHERE name = 'NOME TRIGGER' ), 'AFTER' , 'DDL' ) ) > 1 )  
   RAISERROR ('Trigger NOME TRIGGER nested more than 1 levels.',16,-1)

Ora proviamo prima a inserire un record direttamente nella tabella CarLog.

INSERT INTO CarLog(CarId, CarName)
VALUES (2, 'Civic')

Se selezioniamo tutti i record dalla tabella CarLog, si vedrà che nessun record è stato inserito poiché l’inserimento diretto è impedito dal trigger CarLog_INSERT inserimento.

Ora proviamo a inserire i record tramite la tabella Car.

insert into Car( Name, Make,  Model , Price, Type)
VALUES ('Clio','Renault',2012, 5000,'Sedan')

Quando si inserisce il record precedente nella tabella Car, il trigger Car_INSERT viene eseguito e proverà a inserire un record nella tabella CarLog. Questo a sua volta attiverà il trigger di CarLog_INSERT annidato.

All’ attivazione del Trigger CarLog_INSERT verrà controllato il valore di @@NESTLEVEL del trigger annidato e poiché l’inserimento non è diretto, il record verrà inserito anche nella tabella CarLog.

Conclusione

I trigger nidificati o ricorsivi in SQL Server sono trigger che vengono attivati a seguito dell’esecuzione di altri trigger. In questo articolo abbiamo visto come vengono eseguiti i trigger nidificati o ricorsivi. Abbiamo anche visto come possiamo creare un trigger annidato solo quando viene eseguito indirettamente da altri trigger.

Tutti gli articoli su SQL Server:

https://www.bartolomeoalberico.it/tag/sql-server/

Altri articoli:

• Come ridurre la frequenza di rimbalzo (bounce-rate) del tuo sito web.
• Creare un’unità USB Ubuntu Live con storage persistente in 5 passaggi
• Crittografare HDD con Bitlocker in Windows 10
• Come installare SSH in Windows 10
• Microsoft Paint eliminato nel nuovo aggiornamento di Win 10