Impossible d'utiliser la clause UPDATE avec OUTPUT lorsqu'un déclencheur est sur la table.

Question

Je suis en train de faire un UPDATE avec OUTPUT requête :

UPDATE BatchReports
SET IsProcessed = 1
OUTPUT inserted.BatchFileXml, inserted.ResponseFileXml, deleted.ProcessedDate
WHERE BatchReports.BatchReportGUID = @someGuid

Cette déclaration est très bien, jusqu'à ce qu'un déclencheur soit défini sur la table. Alors mon UPDATE obtiendra l'erreur suivante 334 :

La table cible 'BatchReports' de l'instruction DML ne peut pas avoir de déclencheurs activés si l'instruction contient une clause OUTPUT sans clause INTO.

Ce problème est maintenant expliqué dans un billet de blog par l'équipe de SQL Server -- UPDATE avec clause OUTPUT - Triggers - et SQLMoreResults :

Le message d'erreur est explicite

Et ils donnent aussi des solutions :

L'application a été modifiée pour utiliser la clause INTO.

Sauf que je n'arrive pas à comprendre l'intégralité de l'article du blog.

Alors laissez-moi poser ma question : Qu'est-ce que je dois changer dans mon UPDATE pour qu'il fonctionne ?

Voir aussi

• UPDATE avec clause OUTPUT - Triggers - et SQLMoreResults
• Pourquoi la table cible d'une instruction MERGE ne peut-elle pas avoir de règles activées ?
• L'instruction contient une clause OUTPUT sans erreur de clause INTO.

Answer 1

Avertissement de visibilité : N'est-ce pas le autre réponse . Il donnera des valeurs incorrectes. Lisez la suite pour savoir pourquoi c'est faux.

---

Compte tenu de la complexité nécessaire pour faire UPDATE avec OUTPUT travailler dans SQL Server 2008 R2, j'ai changé ma requête de :

UPDATE BatchReports  
SET IsProcessed = 1
OUTPUT inserted.BatchFileXml, inserted.ResponseFileXml, deleted.ProcessedDate
WHERE BatchReports.BatchReportGUID = @someGuid

à :

SELECT BatchFileXml, ResponseFileXml, ProcessedDate FROM BatchReports
WHERE BatchReports.BatchReportGUID = @someGuid

UPDATE BatchReports
SET IsProcessed = 1
WHERE BatchReports.BatchReportGUID = @someGuid

En gros, j'ai arrêté d'utiliser OUTPUT . Ce n'est pas si mal que Entity Framework lui-même utilise ce même hack !

Avec un peu de chance, 2012 2014 2016 2018 2019 2020 aura une meilleure mise en œuvre.

---

Mise à jour : l'utilisation de OUTPUT est nuisible

Le problème que nous avons rencontré au départ était d'essayer d'utiliser la fonction OUTPUT pour récupérer le "après" valeurs dans un tableau :

UPDATE BatchReports
SET IsProcessed = 1
OUTPUT inserted.LastModifiedDate, inserted.RowVersion, inserted.BatchReportID
WHERE BatchReports.BatchReportGUID = @someGuid

On se heurte alors à la limitation bien connue ( "won't-fix" bogue) dans SQL Server :

La table cible 'BatchReports' de l'instruction DML ne peut pas avoir de déclencheurs activés si l'instruction contient une clause OUTPUT sans clause INTO.

Tentative de contournement n°1

Donc nous essayons quelque chose où nous utiliserons un intermédiaire TABLE pour contenir la variable OUTPUT les résultats :

DECLARE @t TABLE (
   LastModifiedDate datetime,
   RowVersion timestamp, 
   BatchReportID int
)

UPDATE BatchReports
SET IsProcessed = 1
OUTPUT inserted.LastModifiedDate, inserted.RowVersion, inserted.BatchReportID
INTO @t
WHERE BatchReports.BatchReportGUID = @someGuid

SELECT * FROM @t

Sauf que cela échoue parce que vous n'êtes pas autorisé à insérer une timestamp dans la table (même une variable de table temporaire).

Tentative de contournement n°2

Nous savons secrètement qu'un timestamp est en fait un entier non signé de 64 bits (alias 8 octets). Nous pouvons modifier notre définition de la table temporaire pour utiliser binary(8) plutôt que timestamp :

DECLARE @t TABLE (
   LastModifiedDate datetime,
   RowVersion binary(8), 
   BatchReportID int
)

UPDATE BatchReports
SET IsProcessed = 1
OUTPUT inserted.LastModifiedDate, inserted.RowVersion, inserted.BatchReportID
INTO @t
WHERE BatchReports.BatchReportGUID = @someGuid

SELECT * FROM @t

Et ça marche, sauf que les valeurs sont fausses .

L'horodatage RowVersion que nous retournons n'est pas la valeur de l'horodatage tel qu'il existait après la fin de l'UPDATE :

• date de retour : 0x0000000001B71692
• horodatage réel : 0x0000000001B71693

C'est parce que les valeurs OUTPUT dans notre tableau sont pas les valeurs telles qu'elles étaient à la fin de l'instruction UPDATE :

• L'instruction UPDATE commence
  • modifie la ligne
    • l'horodatage est mis à jour (par exemple, 2 → 3)
  • OUTPUT récupère le nouvel horodatage (c'est-à-dire 3)
  • déclencheurs
    • modifie à nouveau la ligne
      • l'horodatage est mis à jour (par exemple, 3 → 4)
• Déclaration UPDATE complète
• retours OUTPUT 3 (la mauvaise valeur)

Cela signifie :

• Nous n'obtenons pas l'horodatage tel qu'il existe à la fin de l'instruction UPDATE ( 4 )
• Au lieu de cela, nous obtenons l'horodatage tel qu'il était au milieu indéterminé de l'instruction UPDATE ( 3 )
• Nous n'obtenons pas l'horodatage correct

Il en va de même pour tout qui modifie tout dans la ligne. Le site OUTPUT ne sortira pas la valeur à la fin de l'UPDATE.

Cela signifie que vous ne pouvez pas faire confiance à OUTPUT pour retourner des valeurs correctes.

Cette réalité douloureuse est documentée dans le BOL :

Les colonnes renvoyées par OUTPUT reflètent les données telles qu'elles sont après la fin de l'instruction INSERT, UPDATE ou DELETE, mais avant l'exécution des déclencheurs.

Comment Entity Framework a-t-il résolu ce problème ?

L'Entity Framework de .NET utilise rowversion pour la Concurrence Optimiste. L'EF dépend de la connaissance de la valeur de la variable timestamp tel qu'il existe après qu'ils aient émis un UPDATE.

Puisque vous ne pouvez pas utiliser OUTPUT pour toute donnée importante, Entity Framework de Microsoft utilise la même solution que moi :

Solution #3 - Final - Ne pas utiliser la clause OUTPUT

Afin de récupérer le après les valeurs, les problèmes d'Entity Framework :

UPDATE [dbo].[BatchReports]
SET [IsProcessed] = @0
WHERE (([BatchReportGUID] = @1) AND ([RowVersion] = @2))

SELECT [RowVersion], [LastModifiedDate]
FROM [dbo].[BatchReports]
WHERE @@ROWCOUNT > 0 AND [BatchReportGUID] = @1

N'utilisez pas OUTPUT .

Oui, il souffre d'une condition de course, mais c'est le mieux que SQL Server puisse faire.

Qu'en est-il des INSERTS ?

Faites ce que fait Entity Framework :

SET NOCOUNT ON;

DECLARE @generated_keys table([CustomerID] int)

INSERT Customers (FirstName, LastName)
OUTPUT inserted.[CustomerID] INTO @generated_keys
VALUES ('Steve', 'Brown')

SELECT t.[CustomerID], t.[CustomerGuid], t.[RowVersion], t.[CreatedDate]
FROM @generated_keys AS g
   INNER JOIN Customers AS t
   ON g.[CustomerGUID] = t.[CustomerGUID]
WHERE @@ROWCOUNT > 0

Encore une fois, ils utilisent un SELECT pour lire la ligne, plutôt que de faire confiance à la clause OUTPUT.

Answer 2

Pour contourner cette restriction, vous devez OUTPUT INTO ... quelque chose, par exemple, déclarer une variable de table intermédiaire comme étant la cible puis SELECT de cela.

DECLARE @T TABLE (
  BatchFileXml    XML,
  ResponseFileXml XML,
  ProcessedDate   DATE,
  RowVersion      BINARY(8) )

UPDATE BatchReports
SET    IsProcessed = 1
OUTPUT inserted.BatchFileXml,
       inserted.ResponseFileXml,
       deleted.ProcessedDate,
       inserted.Timestamp
INTO @T
WHERE  BatchReports.BatchReportGUID = @someGuid

SELECT *
FROM   @T 

Comme indiqué dans l'autre réponse, si votre déclencheur réécrit sur les lignes modifiées par la commande UPDATE de manière à ce qu'elle affecte les colonnes que vous êtes en train de OUTPUT Vous ne trouverez peut-être pas les résultats utiles, mais il ne s'agit que d'un sous-ensemble de déclencheurs. La technique ci-dessus fonctionne bien dans d'autres cas, comme les déclencheurs enregistrant dans d'autres tables à des fins d'audit, ou renvoyant des valeurs d'identité insérées même si la ligne d'origine est réécrite dans le déclencheur.

Answer 3

Pourquoi mettre toutes les colonnes nécessaires dans une variable de table ? Nous avons juste besoin de la clé primaire et nous pouvons lire toutes les données après l'UPDATE. Il n'y a pas de course lorsque vous utilisez la transaction :

DECLARE @t TABLE (ID INT PRIMARY KEY);

BEGIN TRAN;

UPDATE BatchReports SET 
    IsProcessed = 1
OUTPUT inserted.ID INTO @t(ID)
WHERE BatchReports.BatchReportGUID = @someGuid;

SELECT b.* 
FROM @t t JOIN BatchReports b ON t.ID = b.ID;

COMMIT;

Answer 4

Quel est le plan B ?

Parfois, vous avez compilé du code en utilisant la fonction "output inserted", de sorte que l'ajout ultérieur d'un déclencheur pose un problème car vous ne pouvez pas mettre à jour tout votre code existant.