T-SQL: quels COLUMNS ont changé après une mise à jour?

D'ACCORD. Je fais une mise à jour sur une seule ligne dans une table. Tous les champs seront remplacés par de nouveldatatables à l'exception de la key primaire. Cependant, toutes les valeurs ne changeront pas en b / c de la mise à jour. Par exemple, si ma table est la suivante:

TABLE (id int ident, foo varchar(50), bar varchar(50))

La valeur initiale est:

 id foo bar ----------------- 1 hi there

UPDATE tbl SET foo = 'hi', bar = 'something else' WHERE id = 1 ensuite UPDATE tbl SET foo = 'hi', bar = 'something else' WHERE id = 1

Ce que je veux savoir, c'est quelle colonne a changé de valeur et quelle était sa valeur d'origine et quelle est sa nouvelle valeur.

Dans l'exemple ci-dessus, je voudrais voir que la colonne "barre" est passée de "là" à "autre chose".

Possible sans faire une comparaison colonne par colonne? Existe-t-il une déclaration SQL élégante comme EXCEPT qui sera plus fine que juste la ligne?

Merci.

Il n'y a pas de déclaration spéciale que vous pouvez exécuter qui vous dira exactement quelles colonnes ont changé, mais néanless la requête n'est pas difficile à écrire:

 DECLARE @Updates TABLE ( OldFoo varchar(50), NewFoo varchar(50), OldBar varchar(50), NewBar varchar(50) ) UPDATE FooBars SET <some_columns> = <some_values> OUTPUT deleted.foo, inserted.foo, deleted.bar, inserted.bar INTO @Updates WHERE <some_conditions> SELECT * FROM @Updates WHERE OldFoo != NewFoo OR OldBar != NewBar

Si vous essayez de faire quelque chose à la suite de ces changements, il est préférable d'écrire un triggersur:

 CREATE TRIGGER tr_FooBars_Update ON FooBars FOR UPDATE AS BEGIN IF UPDATE(foo) OR UPDATE(bar) INSERT FooBarChanges (OldFoo, NewFoo, OldBar, NewBar) SELECT d.foo, i.foo, d.bar, i.bar FROM inserted i INNER JOIN deleted d ON i.id = d.id WHERE d.foo <> i.foo OR d.bar <> i.bar END

(Bien sûr, vous voudrez probablement faire plus que cela dans un triggersur, mais il y a un exemple d'une action très simplist)

Vous pouvez utiliser COLUMNS_UPDATED au lieu de UPDATE mais je trouve que c'est douloureux, et il ne vous dira toujours pas quelles colonnes ont réellement changé , juste quelles colonnes ont été incluses dans l'instruction UPDATE . Donc, par exemple, vous pouvez écrire UPDATE MyTable SET Col1 = Col1 et il vous dira Col1 même que Col1 été mis à jour même si pas une seule valeur n'a réellement changé. Lorsque vous écrivez un triggersur, vous devez réellement tester les valeurs individuelles avant et après pour vous assurer que vous obtenez des changements réels (si c'est ce que vous voulez).

PS Vous pouvez aussi UNPIVOT comme le dit Rob, mais vous aurez toujours besoin de spécifier explicitement les colonnes dans la clause UNPIVOT , ce n'est pas magique.

Essayez d'annuler le détourage à la fois inséré et supprimé, puis vous pouvez join, en cherchant où la valeur a changé.

Vous pouvez détecter cela dans un triggersur ou utiliser CDC dans SQL Server 2008.

Si vous créez un triggersur FOR AFTER UPDATE la table inserted contiendra les lignes avec les nouvelles valeurs et la table deleted contiendra les lignes correspondantes avec les anciennes valeurs.

Si vous utilisez SQL Server 2008, vous devriez probablement jeter un coup d'œil à la nouvelle fonctionnalité Change Data Capture. Cela fera ce que vous voulez.

 OUTPUT deleted.bar AS [OLD VALUE], inserted.bar AS [NEW VALUE]

@Calvin Je me suis juste basé sur l'exemple UPDATE. Je ne dis pas que c'est la solution complète. Je donnais un indice que vous pourriez le faire quelque part dans votre code 😉

Puisque j'ai déjà obtenu un -1 à partir de la réponse ci-dessus, permettez-moi de lancer ceci dans:

Si vous ne savez pas vraiment quelle Colonne a été mise à jour, je dirais créer un triggersur et utiliser la fonction COLUMNS_UPDATED () dans le corps de ce triggersur (Voir ceci )

J'ai créé dans mon blog une reference Bitmask à utiliser avec cette COLUMNS_UPDATED (). Cela vous facilitera la vie si vous décidez de suivre ce path (Trigger + Columns_Updated ())

Si vous n'êtes pas familier avec Trigger, voici mon exemple de base Trigger http://dbalink.wordpress.com/2008/06/20/how-to-sql-server-sortinggger-101/

Une autre option pour suivre les modifications de données consiste à écrire des données dans une autre table (éventuellement temporaire), puis à parsingr la différence à l'aide de XML. Les données modifiées sont en cours d'écriture dans la table d'audit avec les noms de colonnes. Une seule chose est que vous devez connaître les champs de table pour préparer la table temporaire. Vous pouvez find cette solution ici:

partie 1
partie 2