OUTPUT Clause and Triggers

Recentemente ho avuto l’occasione di apprezzare la clausola OUTPUT per determinare il valore degli identificativi univoci (ID) per una colonna con proprietà IDENTITY, per le righe aggiunte a una tabella durante un’operazione di INSERT.

Come indicato sui books online, la clausola OUTPUT, implementata con SQL Server 2005:

“Restituisce le informazioni da (o le espressioni basate su) ogni riga interessata da un’istruzione INSERT, UPDATE, DELETE o MERGE. Questi risultati possono essere restituiti all’applicazione di elaborazione per l’utilizzo nei messaggi di errore, l’archiviazione e altri scopi simili dell’applicazione. I risultati possono anche essere inseriti in una tabella o in una variabile tabella. Inoltre, è possibile acquisire i risultati di una clausola OUTPUT in un’istruzione nidificata INSERT, UPDATE, DELETE o MERGE e inserire tali risultati in una vista o tabella di destinazione“.

Ecco un esempio (opportunamente semplificato) di utilizzo della clausola OUTPUT, ipotizziamo di avere a disposizione la tabella dbo.TableA e di voler conoscere, per ogni statement di tipo INSERT, il valore della colonna ID e della colonna ColA, della tabella dbo.TableA, per ogni riga inserita.

Il seguente frammento di codice in linguaggio T-SQL esegue il setup della tabella dbo.TableA sul database di sistema tempdb.

USE [tempdb];
GO

-- Creazione tabella dbo.TableA
CREATE TABLE dbo.TableA
(
  ID INTEGER IDENTITY(1, 1) NOT NULL PRIMARY KEY
  ,ColA VARCHAR(64)
);
GO

-- Inserimento dati di prova con restituzione delle informazioni inserite
INSERT INTO dbo.TableA
(ColA) OUTPUT inserted.ID, inserted.ColA VALUES ('Row 1'), ('Row 2');
GO

Figura 1 – Inserimento dati di prova con restituzione delle informazioni inserite.

Ipotizziamo ora che questo comando di INSERT sia stato utilizzato all’interno di un’applicazione, in seguito distribuita a un cliente. Dopo qualche tempo, il servizio di assistenza riceve una segnalazione di errore proveniente dal cliente presso il quale è stato fatto il deploy dell’applicazione.

Il messaggio di errore restituito è quello illustrato nella figura seguente, riprodotto su SQL Server Management Studio:

Figura 2 – SQL Server Error Message 334

Che cosa è successo all’applicazione? Che è in produzione già da molto tempo senza alcun problema? Non sono state fatte modifiche al codice sorgente! Queste sono le prime domande che uno sviluppatore, tipicamente, si pone in questi casi.

Uno dei comandi di INSERT, inviati dall’applicazione a SQL Server, ora fallisce perché è stato creato un oggetto trigger, attivo sulla tabella dbo.TableA (per l’evento INSERT).

La clausola OUTPUT, con la sintassi illustrata in figura 2, non può restituire informazioni relative alle righe inserite proprio per la presenza del trigger. Il messaggio di errore suggerisce di utilizzare la clausola INTO per memorizzare le informazioni riguardanti le righe inserite in una variabile di tipo tabella oppure in una tabella temporanea.

Il seguente frammento di codice T-SQL contiene i comandi di creazione del trigger TR_LogTableA e della tabella di log dbo.LogTableA su cui il trigger tenta, a sua volta, di inserire righe.

USE [tempdb];
GO

-- Creazione tabella dbo.LogTableA
CREATE TABLE dbo.LogTableA
(
LogID INTEGER NOT NULL
,LogColA VARCHAR(64)
,Operation VARCHAR(1) NOT NULL DEFAULT 'I'
);
GO

-- Creazione trigger TR_LogTableA
CREATE TRIGGER TR_LogTableA on dbo.TableA
FOR INSERT
AS BEGIN
  /*
    Log degli INSERT eseguiti sulla tabella dbo.TableA
  */

  INSERT INTO dbo.LogTableA
  (
  LogID
  ,LogColA
  ,Operation
  )
  SELECT
  ID
  ,ColA
  ,'I'
  FROM
  INSERTED
END;
GO

Dopo aver adeguato il codice dell’applicazione attraverso l’utilizzo della clausola INTO, abbiamo cercato di comprendere i motivi dell’errore, che sono da ricercarsi nel numero di result set restituiti per lo statement.

Il codice ODBC, per eseguire la restituzione dei result set, utilizza cicli di chiamate alle seguenti API: SQLFetch, SQLNumResultCols, SQLRowCount, SQLMoreResults.

Con il trigger attivo, il primo result set restituito riguardava il numero di righe inserite dal trigger e non il numero di righe inserite dal comando che ha scatenato il trigger! Il secondo result set riguardava il numero di righe interessate dal nostro comando di INSERT (quello eseguito dall’applicazione) e l’ultimo result set riguardava le informazioni relative alle righe inserite dallo statement che ha scatenato il trigger, sostanzialmente quello volevamo in primo luogo!

Conclusioni

Ricordiamoci che l’implementazione di un trigger avrà effetto sugli statement INSERT, UPDATE, DELETE e MERGE qualora in questi ultimi venga utilizzata la clausola OUTPUT. Per evitare questi potenziali errori si può usare la clausola INTO specificando la destinazione dell’output. L’applicazione dovrà essere progettata in modo da poter leggere tutti i result set restituiti. Una best practices, oltre a quella descritta in questo articolo, da ricordare quando di realizza un trigger è quella di aggiungere lo statement SET NOCOUNT ON per evitare venga restituito il result set che descrive il numero di righe interessare dalle operazioni eseguite nel trigger, tipicamente il testo “affected number of rows”.