Home > FAQ > E’ possibile disabilitare un Trigger dall’interno di un altro?

E’ possibile disabilitare un Trigger dall’interno di un altro?

Quale effetto produce l’esecuzione di un comando DDL per disabilitare un trigger presente sulla stessa tabella su cui è presente il trigger che lo invoca ?

Sembra un gioco di parole, ma chi ha realizzato il trigger che ho controllato poco tempo fa non si è posto questa domanda.

La segnalazione è arrivata da alcuni utenti dell’ufficio commerciale, dopo un intervento tecnico hanno iniziato a lamentare pesanti rallentamenti proprio quando era in corso l’inserimento di uno o più ordini di vendita.

Abbiamo verificato la procedura di inserimento ordini e abbiamo esaminato i trigger sulla tabella dbo.orderheader, uno di questi eseguiva la disattivazione di un altro trigger che si scatenava sull’evento ON UPDATE della stessa tabella.

Il comando che permette di disattivare un trigger è ALTER TABLE <nome_tabella> DISABLE TRIGGER <nome_trigger> oppure per disattivare tutti gli oggetti trigger di una tabella si può utilizzare ALTER TABLE <nome_tabella> DISABLE TRIGGER ALL.

L’esempio seguente riproduce l’anomalia, generiamo la tabella dbo.orderheader sul database AdventureWorks:

use [AdventureWorks];

go

-- create table orderheader
create table dbo.orderheader
(orderid int identity(1, 1) not null,
 orderdate datetime default getdate() not null,
 ordernumber as (isnull(N'SO' + convert([nvarchar](23), [orderid], 0), N'*** error ***')),
 customerid int default 1 not null,
 shipname varchar(20) default 'name',
 shipdate datetime null,
 shipaddress varchar(40) default 'address',
 shipvia varchar(40) default 'via',
 shipcity varchar(20) default 'city',
 shipregion varchar(20) default 'region',
 shippostalcode varchar(20) default 'postal code',
 shipcountry varchar(20) default 'country',
 -- delivery info
 deliverydate datetime default (getdate() + datepart(ss, getdate())) not null,
 deliverynote varchar(40)
 primary key(orderid)
);

go

Con i seguenti comandi T-SQL generiamo un trigger per l’evento ON INSERT ed uno per l’evento ON UPDATE, per semplicità, in questo esempio, i trigger eseguono solo semplici SELECT rispettivamente sulle variabili @@VERSION e @@SPID.

if object_id('dbo.tr_orderheader_upd_shipdate', 'TR') is not null
  drop trigger dbo.tr_orderheader_upd_shipdate;

go

create trigger dbo.tr_orderheader_upd_shipdate on dbo.orderheader
for update
as begin
  /*
    Descrizione: Aggiorna la colonna shipdate
  */
  
  -- T-SQL statement for update shipdate
  -- ...
  -- ...
  select
    @@SPID
end
go

if object_id('dbo.tr_orderheader_disable_tr', 'TR') is not null
  drop trigger dbo.tr_orderheader_disable_tr;
go
create trigger dbo.tr_orderheader_disable_tr on dbo.orderheader
for insert
as begin
  /*
    Descrizione: "alter table ... disable trigger" all'interno di un trigger
  */

  -- Disattiva il trigger tr_orderheader_upd_shipdate
  alter table dbo.orderheader disable trigger tr_orderheader_upd_shipdate

  -- T-SQL statements
  -- ...
  select
    @@version

  -- T-SQL per monitorare i lock provocati dal comando di
  -- alter table ... disable trigger
  select
    (
      select
        o.name
      from
        sys.sysobjects o
      where
        (o.id=s.resource_associated_entity_id) and
        (s.resource_type='OBJECT') 
    ) as object_name
    ,s.resource_type
    ,s.resource_database_id
    ,s.request_mode
    ,s.request_type
    ,s.request_reference_count
    ,s.request_lifetime
    ,s.request_session_id
  from
    sys.dm_tran_locks s
  where
    (s.request_session_id = @@SPID)

  -- Attiva il trigger tr_orderheader_upd_shipdate
  alter table dbo.orderheader enable trigger tr_orderheader_upd_shipdate
end

go

Nel trigger dbo.tr_orderheader_disable_tr viene interrogata la DMV sys.dm_tran_locks che fornisce importanti informazioni sulle risorse correntemente bloccate e sui relativi processi richiedenti il blocco, la query è stata aggiunta per monitorare i lock durante l’esecuzione del trigger, il dataset viene restituito in output.

Vediamo ora cosa accade durante l’inserimento di un nuovo ordine cliente (per semplicità viene inserito solo il record di testata con i valori di default).

-- insert dbo.orderheader
insert into dbo.orderheader default values;
go

Osserviamo il seguente output:

Disable Trigger All dentro un Trigger Img1

La tabella dbo.orderheader è interessata da un lock in modalità schema modification locks (Sch-M), questo tipo di lock viene applicato ad una tabella quanto quest’ultima è interessata da un’operazione DDL (data definition language) come ad esempio l’aggiunta di una colonna. Durante il tempo in cui è detenuto, il blocco Sch-M impedisce l’accesso simultaneo alla tabella. Questo significa che il blocco Sch-M impedisce tutte le operazioni al di fuori della transazione corrente fino a quando il blocco viene rilasciato… da qui nascono le lamentele degli utenti dell’ufficio commerciale.

Questi i lock applicati durante il comando di INSERT dopo aver eliminato le operazioni DDL:

Disable Trigger All dentro un Trigger Img2

Conclusioni

In generale, sono da preferire le soluzioni che non fanno uso di trigger.

Qualora, però, non si possa fare a meno di implementare un nuovo trigger è necessario verificare (prima di iniziare a scrivere il codice) l’eventuale presenza di altri oggetti trigger sulla stessa tabella, dove possibile è consigliato l’accorpamento. Evitare di disattivare un trigger all’interno di un altro solo perché non si conosce la logica del primo e si teme che possa interferire con il nuovo trigger che si sta per implementare.

Pulizia DB

use [AdventureWorks];
go

if (object_id('orderheader', 'U') is not null)
  drop table dbo.orderheader;
go

 

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

Usare json su SQLServer 2005, 2008, 2012 e 2014 .. è possibile!

Vedere il supporto nativo di JSON su SQLServer 2016 (e anche su Azure SQLDatabase) mi …