Home > Articoli > OUTPUT Clause and Triggers

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

Eseguiamo ora un’operazione di INSERT aggiungendo la clausola OUTPUT in modo che siano restituite le informazioni richieste relative alle righe inserite; la figura 1 illustra l’output (esattamente come lo attendiamo) del seguente statement di tipo INSERT:

-- 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

OUTPUT Clause and Triggers img1

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:

OUTPUT Clause and Triggers img2

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”.

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

String or Binary data would be truncated: replacing the infamous error 8152 … also in SQL Server 2017

Come già annunciato allo scorso Ignite, una delle nuove (tra le tante) feature di SQL …