Esporta (0) Stampa
Espandi tutto

Il primo comando di ogni Trigger

di Sergio Govoni - Microsoft MVP

Blog: http://www.ugiss.org/sgovoni/

Twitter: @segovoni

Dn376356.7B654F178A3842F7F616A829DC6DF588(it-it,MSDN.10).png

Agosto, 2013

Un oggetto Trigger, di SQL Server, è ottimizzato se la sua durata è breve. I Trigger lavorano sempre in transazione (implicita o esplicita che sia) e i Lock rimarranno al loro posto fino a quando la transazione non verrà confermata (con un COMMIT) o respinta (con un ROLLBACK). Si intuisce facilmente che più lunga sarà la durata di un Trigger, più alta sarà la probabilità che esso blocchi un altro processo.

Ottimizzare un Trigger esistente, che racchiude centinaia di righe di codice, può rivelarsi molto difficile; quindi l’arma migliore è la prevenzione.

Potreste considerarlo banale, ma lo troverete interessante, la prima cosa da fare affinché la durata di un Trigger sia breve è stabilire se il Trigger debba intervenire oppure no. Se non ci sono righe coinvolte nel (precedente) comando che l’ha scatenato, significa generalmente che il Trigger in questione non dovrà fare nulla.

L’unica cosa, quindi, che si dovrebbe sempre fare all’interno di un Trigger è verificare il valore della variabile di sistema @@ROWCOUNT (o della funzione ROWCOUNT_BIG se il numero di righe è superiore a 2 miliardi) per controllare se nel precedente comando (tipicamente INSERT, UPDATE o DELETE) sono state coinvolte righe. Se ciò non è accaduto (@@ROWCOUNT = 0) non c’è nulla che il Trigger possa fare se non restituire il controllo al chiamate con l’istruzione RETURN (T-SQL).

Il seguente frammento di codice T-SQL dovrebbe quindi comparire all’inizio di ogni Trigger:

IF (@@ROWCOUNT = 0)
  RETURN;

Ecco un esempio di creazione di un Trigger, per il comando UPDATE, sulla tabella Person.Person del database AdventureWorks2012:

CREATE TRIGGER TR_Person_Person_Upd ON Person.Person
FOR UPDATE
AS BEGIN
  --
  IF (@@ROWCOUNT = 0)
    RETURN;
  -- ...
  -- ...
  -- ...
END;

La verifica di @@ROWCOUNT permette anche di stabilire se il numero di righe coinvolte è quello che ci si aspetta. In tutti i casi in cui ci si aspetta venga coinvolta solo una riga, dovrà essere eseguito il test:

IF (@@ROWCOUNT = 1)
  <azione>

Dopo aver superato il test riguardante le righe coinvolte, si potrebbe verificare se le colonne di nostro interesse sono state aggiornate (ovviamente solo nel caso di Trigger per il comando UPDATE), utilizzando l’istruzione:

IF UPDATE(<nome_colonna>)
  <azione>

Nel caso le colonne di nostro interesse non siano state aggiornate, avremmo un’altra occasione per restituire il controllo al chiamante, mantenendo breve la durata del Trigger.

La funzione UPDATE() restituisce il valore Booleano True nel caso la colonna sia stata modificata o nel caso di INSERT, ma non permette di sapere, in caso di aggiornamento, se i valori della colonna sono cambiati ovvero se il vecchio valore è diverso dal nuovo. Qualora il Trigger debba eseguire un’azione solo se il vecchio valore, di una colonna, risulti essere diverso dal nuovo, si potranno interrogare le tabelle virtuali INSERTED e DELETED. Il seguente frammento di codice T-SQL implementa un esempio di Trigger, per il comando UPDATE, sulla tabella Sales.SalesOrderDetail del database AdventureWorks2012 dove si desidera eseguire un’azione solo quando viene aggiornata la colonna UnitPrice e il vecchio valore è diverso dal nuovo per almeno una riga.

CREATE TRIGGER Sales.TR_SalesOrderDetail_Upd ON Sales.SalesOrderDetail
AFTER UPDATE AS 
BEGIN
  IF (@@ROWCOUNT = 0)
    RETURN;

  SET NOCOUNT ON;

  IF UPDATE(UnitPrice)
     AND EXISTS (SELECT i.SalesOrderID, i.SalesOrderDetailID
                 FROM inserted AS i
                 JOIN deleted AS d ON
                            (d.SalesOrderID=i.SalesOrderID)
                            AND (d.SalesOrderDetailID=i.SalesOrderDetailID)
                 WHERE (d.UnitPrice <> i.UnitPrice))
  BEGIN
    -- <azione>
  END;
END;
GO

Si osservi l’istruzione “SET NOCOUNT ON” eseguita immediatamente dopo aver verificato il valore della variabile @@ROWCOUNT; permette di bloccare l’invio di messaggi al client per ogni istruzione all’interno del Trigger. Nel caso di Trigger contenenti diverse istruzioni e che non restituiscono un’elevata quantità di dati, l'impostazione di SET NOCOUNT su ON può determinare un incremento delle prestazioni significativo grazie alla notevole riduzione del traffico di rete.

I Trigger sembrano facili da scrivere, ma scrivere Trigger efficienti non è affatto semplice e quando la loro complessità aumenta, talvolta possono presentare effetti collaterali in grado di confondere persino l’autore. L’importante è ricordarsi che un Trigger è ottimizzato se la sua durata è breve.



di Sergio Govoni - Microsoft MVP

Blog: http://www.ugiss.org/sgovoni/

Twitter: @segovoni


Mostra:
© 2014 Microsoft