Primi e Ultimi Trigger

Qualora una tabella avesse molteplici trigger AFTER sullo stesso tipo di comando INSERT, UPDATE o DELETE, vi siete mai chiesti quale fosse l’ordine di attivazione?

Scenario

Ipotizziamo di utilizzare la tabella dbo.Product su cui insistono alcuni (tre) trigger AFTER di tipo INSERT. Con il seguente frammento di codice T-SQL eseguiamo il setup della tabella dbo.Product sul database di esempio AdventureWorks.

use [AdventureWorks];
go

-- Setup table dbo.Product
if OBJECT_ID('dbo.Product', 'U') is not null
  drop table dbo.Product;
go

create table dbo.Product
(
  ProductID varchar(25) not null
  ,LocationID varchar(20) not null
  ,Quantity decimal(9, 2) not null
  ,ModifiedDate date not null default getdate()
  ,Status bit not null default 1
  ,constraint PK_Product_ProductID_LocationID primary key(ProductID, LocationID)
);
go

Dopo aver definito la struttura della tabella dbo.Product eseguiamo la creazione dei trigger di tipo INSERT, per semplicità ogni trigger eseguirà il PRINT di un messaggio informativo che ci permetterà di analizzare l’ordine di attivazione.

-- Create triggers on dbo.Product
create trigger dbo.tr_Product_INSERT_1
  on dbo.Product AFTER INSERT
as
  print 'Fire trigger dbo.tr_Product_INSERT_1';
go

create trigger dbo.tr_Product_INSERT_2
  on dbo.Product AFTER INSERT
as
  print 'Fire trigger dbo.tr_Product_INSERT_2';
go

create trigger dbo.tr_Product_INSERT_3
  on dbo.Product AFTER INSERT
as
  print 'Fire trigger dbo.tr_Product_INSERT_3';
go

FAQ

Come possiamo garantire l’ordine di attivazione dei trigger INSERT definiti nella tabella dbo.Product ?

La stored procedure di sistema sp_settriggerorder permette di stabilire, per ogni tipo, il primo e l’ultimo trigger (ad essere attivato) per ciascuna tabella.

Eseguiamo l’inserimento di un prodotto nella tabella dbo.Product, ci aspettiamo vengano attivati, in sequenza, i trigger appena definiti…

insert into dbo.Product
(
  ProductID
  ,LocationID
  ,Quantity
  ,ModifiedDate
)
values
(
  'RAVIOLI'
  ,'B002'
  ,10
  ,'20100921'
);
go

…ed ecco l’ordine di attivazione (by default), che in questo caso coincide con l’ordine di creazione:

Fire trigger dbo.tr_Product_INSERT_1
Fire trigger dbo.tr_Product_INSERT_2
Fire trigger dbo.tr_Product_INSERT_3

(Righe interessate: 1)

Ipotizziamo sia necessario garantire che il trigger dbo.tr_Product_INSERT_3 sia il primo (dei tre) ad attivarsi ad ogni INSERT rivolto alla tabella prodotti, utilizziamo quindi la stored procedure di sistema sp_settriggerorder per specificare a SQL Server il primo trigger da attivare in corrispondenza di un comando di INSERT, come illustrato nel seguente frammento di codice T-SQL:

exec sp_settriggerorder
  @triggername = 'dbo.tr_Product_INSERT_3'
  ,@order = 'First'
  ,@stmttype = 'INSERT';
go

Inseriamo un altro record nella tabella prodotti e verifichiamo l’ordine di attivazione di trigger, ci aspettiamo che il primo, ad attivarsi, sia dbo.tr_Product_INSERT_3.

insert into dbo.Product
(
  ProductID
  ,LocationID
  ,Quantity
  ,ModifiedDate
)
values
(
  'CHOCOLADE'
  ,'F015'
  ,5
  ,'20100921'
);
go

Effettivamente l’ordine di attivazione è cambiato, il primo trigger ad attivarsi è proprio dbo.tr_Product_INSERT_3:

Fire trigger dbo.tr_Product_INSERT_3
Fire trigger dbo.tr_Product_INSERT_1
Fire trigger dbo.tr_Product_INSERT_2

(Righe interessate: 1)

In modo analogo possiamo garantire anche l’attivazione dell’ultimo trigger (in corrispondenza di un comando di INSERT), che in questo esempio imposteremo con dbo.tr_Product_INSERT_1.

-- Set the last trigger on dbo.Product
exec sp_settriggerorder
  @triggername = 'dbo.tr_Product_INSERT_1'
  ,@order = 'Last'
  ,@stmttype = 'INSERT';
go

Inseriamo un altro record nella tabella prodotti e verifichiamo nuovamente l’ordine di attivazione dei trigger:

insert into dbo.Product
(
  ProductID
  ,LocationID
  ,Quantity
  ,ModifiedDate
)
values
(
  'MASCARPONE DOC'
  ,'D214'
  ,30
  ,'20100921'
);
go

Fire trigger dbo.tr_Product_INSERT_3
Fire trigger dbo.tr_Product_INSERT_2
Fire trigger dbo.tr_Product_INSERT_1

(Righe interessate: 1)

L’output ottenuto dimostra la possibilità di garantire l’attivazione del primo e dell’ultimo trigger, per ogni tipo, per ciascuna tabella; gli altri trigger verranno eseguiti in base a un ordine non definito.

Pulizia DB

-- Cleanup
use [AdventureWorks];
go

-- dbo.Product
if OBJECT_ID('dbo.Product', 'U') is not null
  drop table dbo.Product;
go