MERGE and Triggers

Abbiamo parlato del comando MERGE nell’articolo MERGE Statements in Action, ora invece tratteremo un approfondimento che ci permetterà di comprendere le regole di attivazione degli eventuali trigger presenti nella tabella target, oggetto delle operazioni DML specificate nelle clausole del comando MERGE.

L’esecuzione delle azioni specificate nelle clausole del comando MERGE scatena l’attivazione dei trigger come una qualsiasi operazione DML. Esiste però una particolarità interessante: verrà scatenata una sola occorrenza di ogni trigger, in risposta al comando MERGE, anche in presenza di esecuzioni multiple della stessa azione da parte di quest’ultimo. Come sempre, i trigger avranno accesso alle tabelle virtuali inserted e deleted che conterranno tutte le righe modificate dallo statement.

Dimostreremo questa particolarità del comando MERGE utilizzando lo scenario di esempio già precedentemente descritto in MERGE Statements in Action.

Ipotizziamo di eseguire l’inventario dei prodotti giacenti a magazzino; e di dover aggiornare le giacenze (logiche) memorizzate nella tabella dbo.Product del database AdventureWorks con le giacenze (fisiche) rilevate dagli operatori durante l’inventario. Il comando MERGE sarà di grande aiuto per aggiornare i dati contenuti nella tabella dbo.Product (target).

Si richiede quindi di:

• Aggiornare le quantità e inserire i nuovi prodotti (con le relative giacenze)
• Eliminare i prodotti non giacenti (giacenza = 0)
• Aggiornare la colonna status per i prodotti non rilevati (righe in target che non hanno corrispondenza in source)

Procediamo con la creazione della tabella dbo.Product sul database AdventureWorks ed inseriamo alcuni dati di prova.

use [AdventureWorks];
go

-- Setup table & insert data

-- 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)
);

insert into dbo.Product
(ProductID, LocationID, Quantity, ModifiedDate)
values
('ravioli angelo', 'b002', 10, '20100101'),
('chocolade',      'f015',  5, '20100101'),
('ipoh coffee',    'h001', 70, '20100201'),
('mascarpone doc', 'd214', 30, '20100201');
go

select * from dbo.Product;
go

Figura 1 – Dati contenuti nella tabella dbo.Product

Per analizzare il comportamento di eventuali trigger in presenza del comando MERGE, definiamo sulla tabella (target) dbo.Product i seguenti trigger rispettivamente per le azioni INSERT, UPDATE e DELETE:

-- Create triggers on dbo.Product

create trigger dbo.tr_Product_INSERT on dbo.Product AFTER INSERT
as
  print 'INSERT detected on dbo.Product';
go

create trigger dbo.tr_Product_UPDATE on dbo.Product AFTER UPDATE
as
  print 'UPDATE detected on dbo.Product';
go

create trigger dbo.tr_Product_DELETE on dbo.Product AFTER DELETE
as
  print 'DELETE detected on dbo.Product';
go

I trigger eseguiranno semplicemente il comando print(…) per segnalare l’azione rilevata.

Ipotizziamo di raccogliere le rilevazioni inventariali nella tabella dbo.FrequentInventory, che possiamo creare (sul database AdventureWorks) e popolare con alcuni dati di test attraverso il seguente frammento di codice T-SQL:

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

create table dbo.FrequentInventory
(
  ProductID varchar(25) not null
  ,LocationID varchar(20) not null
  ,Quantity decimal(9, 2) not null
  ,ModifiedDate date not null default getdate()
  ,constraint PK_FrequentInventory_ProductID_LocationID primary key(ProductID, LocationID)
);
go

insert into dbo.FrequentInventory
(ProductID, LocationID, Quantity, ModifiedDate)
values
('CHOCOLADE',         'F015',  7, '20100312'),
('GORGONZOLA TELINO', 'F001', 22, '20100312'),
('SEATTLE CRAB',      'G004', 80, '20100312'),
('MASCARPONE DOC',    'D214',  0, '20100301');

Consultiamo le rilevazioni inventariali:

select * from dbo.FrequentInventory;
go

Figura 2 – Rilevazioni inventariali memorizzate nella tabella dbo.FrequentInventory

Eseguiamo il seguente comando MERGE in cui sono state specificate tutte le clausole, compresa la clausola WHEN NOT MATCHED BY SOURCE proprietaria del linguaggio T-SQL. Si noti che l’operazione di UPDATE verrà eseguita sia dalla clausola WHEN MATCHED (per il prodotto “chocolade” la cui giacenza viene aggiornata da 5 PZ a 7 PZ) che dalla clausola WHEN NOT MATCHED BY SOURCE (per i prodotti “ipoh coffee” e “ravioli angelo”).

-- MERGE statement

begin transaction;
go

merge into
  dbo.Product as itarget
using
  dbo.FrequentInventory as isource
    on ((itarget.ProductId = isource.ProductId)
        and (itarget.LocationId = isource.LocationId))

when matched AND
  (isource.Quantity <> 0)
  and ((itarget.Quantity <> isource.Quantity)
       or (itarget.ModifiedDate <> isource.ModifiedDate)) then
  update set
    itarget.Quantity = isource.Quantity
    ,itarget.ModifiedDate = isource.ModifiedDate

when matched AND
  (isource.Quantity = 0) then
  delete

when not matched then
  insert
  (
    ProductId
    ,LocationId
    ,Quantity
    ,ModifiedDate
  )
  values
  (
    isource.ProductId
    ,isource.LocationId
    ,isource.Quantity
    ,isource.ModifiedDate
  )

when not matched by source then
  update set
    status = 0;
go

select * from dbo.Product;
go

rollback transaction;
go

Oltre all’aggiornamento dei dati, si ottiene il seguente output:

INSERT detected on dbo.Product

UPDATE detected on dbo.Product

DELETE detected on dbo.Product

Conclusioni

Come descritto precedentemente, l’azione di UPDATE è stata attivata da due differenti clausole, ma ha provocato solo una occorrenza del trigger dbo.tr_Product_UPDATE, per tutte le righe modificate. Questo comportamento è in accordo con il fatto che in SQL Server i trigger vengono attivati per ogni statement.