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

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:

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:

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