Home > Articoli > MERGE and Triggers

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

Consultiamo i dati inseriti, che rappresentano le giacenze (logiche) dei prodotti in magazzino:

select * from dbo.Product;
go

MERGE in Action Img2

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

MERGE in Action Img3

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.

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto. Lavora con SQL Server dalla versione 7.0 e si è occupato d'implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving. Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server. Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional). Nel corso dell'anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

Automazione delle attività di manutenzione in Azure SQL Database (2 Parte)

Introduzione Nell’articolo Automazione delle attività di manutenzione in Azure SQL Database abbiamo descritto le attività …