Une ch'tite bizarrerie dans les Updates de SQL Server

Version imprimable

16/03/2004, 16h31
Wakko2k

Une ch'tite bizarrerie dans les Updates de SQL Server

Voici la ptite bizarrerie : je dois lorsque certains champs d'un tuple de la table gsm sont modifies, enregistrer dans une table historique le changement pour ce faire je pense donc a un sympathique petit trigger dans ce genre

IF (UPDATE ("user_id") OR ... OR UPDATE (gsm_status))
BEGIN
INSERT INTO History_Gsm (...)
VALUES (current_timestamp, ...)
END

Ce qui est fort sympathique pcq ca marche dans le sql query analyser :D

La ou de sombres nuages se profilent c'est lorsque je fais l'update via une page web, je dois donc faire
UPDATE TGsm SET user_id = mavariable, ... = monautrevariable, ... gsm_Status = madernierevar
WHERE gsm_id = uneautrevariable

la ou ca devient drole c'est que personellement je m'attendais à ce que sql server me dise tel champ a ete modifie et tel autre non, et bah quenini il me dit qu'ils ont tous été modifié :(

Du coup si je modifie la date d'achat du gsm par exemple et bien pour lui il doit le mettre dans l'historique vu que user_id et gsm_Status ont aussi changé :/

Qqun a une idée de comment faire pour qu'il trouve lui même comme un grans que tel ou tel champs a vraiment changé ou je dois m'amusé à comparer ca moi même comme un gros lourd?

Merci :)
16/03/2004, 16h40
Gandalf Le Blanc

Slt,

Il faut utiliser:

Citation:

IF (COLUMNS_UPDATED())

Teste, dans un déclencheur INSERT ou UPDATE uniquement, si la ou les colonne(s) mentionnée(s) ont été insérées ou mises à jour. COLUMNS_UPDATED renvoie un modèle de bit de type varbinary qui indique quelles colonnes de la table ont été insérées ou mises à jour.

La fonction COLUMNS_UPDATED renvoie les bits dans l'ordre de gauche à droite, le bit le moins significatif étant celui le plus à gauche. Le bit le plus à gauche représente la première colonne de la table, le suivant la deuxième colonne, et ainsi de suite. COLUMNS_UPDATED renvoie plusieurs octets si la table sur laquelle le déclencheur est créé contient plus de 8 colonnes, l'octet le moins significatif étant celui le plus à gauche. COLUMNS_UPDATED renvoie la valeur TRUE pour toutes les colonnes dans les actions INSERT car les colonnes possèdent des valeurs explicites ou implicites (NULL) insérées.

COLUMNS_UPDATED peut être utilisé à tout endroit, à l'intérieur du corps du déclencheur.

a+
16/03/2004, 17h53
Wakko2k

dans gsm.onUpdate

IF (COLUMNS_UPDATED() & 4) > 0
BEGIN
INSERT INTO History_Gsm (mes brols a audité...)
VALUES (current_timestamp, etc...)
END
END

sachant que les champs de ma table gsm sont [gsm_id, gsm_model, user_id, ...] je suppose que mon instruction consiste à vérifier si la colonne user_id a bien été modifié et bien il me fait rien ce glandeur (ou alors c'est moi le con :p)

Entre () ca a l'air bien sympathique quand même mais ma table elle est bien plus "large" que 8 colonnes, je fais comment alors?
16/03/2004, 18h06
Gandalf Le Blanc

Il y a des exemples dans la doc SQL ...

Citation:

F. Utiliser COLUMNS_UPDATED pour tester plus de 8 colonnes
Si vous devez détecter les mises à jour éventuellement apportées aux colonnes d'une table autres que les 8 premières, vous devez utiliser la fonction SUBSTRING pour tester le bit adéquat renvoyé par COLUMNS_UPDATED. Cet exemple détecte les mises à jour éventuellement apportées aux colonnes 3, 5 et 9 de la table Northwind.dbo.Customers.

USE Northwind
DROP TRIGGER tr1
GO
CREATE TRIGGER tr1 ON Customers
FOR UPDATE AS
IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(3-1))
+ power(2,(5-1)))
AND (SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(1-1)))
)
PRINT 'Columns 3, 5 and 9 updated'
GO

UPDATE Customers
SET ContactName=ContactName,
Address=Address,
Country=Country
GO
08/04/2004, 12h01
Wakko2k

j'ai galere longtemps avec diverse technique et je n'y arrive tout simplement pas... je reposte ce petit message pour que qqun puisse à nouveau le lire et peut etre m'aider :(
Pour l'instant je penche vers un INSTEAD OF UPDATE qui fontionne pour detecter si oui ou non il y a eu réel modification sur tel ou tel champ mais bon, avec cette tehcnique j'ai un autre problème, comment je fais pour updater ma table TGSM réellement alors? Vu qu'un update va declencher mon instead of update et ensuite si dans le instead of update je demande de faire l'update aussi sur TGSM ca va redeclencher le mm instead of udpate etc etc...
donc c'est la galère :'(
Je ne vois que comme dernière solution que le client, fasse lui même un update dans ma tblae d'historique mais la franchement, je trouve ca débile...

Amicalement,
Hasan

voila un exemple, testez le et vous verrez pe plus clairement :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
CREATE TABLE TPlop (
	id numeric(2) PRIMARY KEY,
	fname varchar(20),
	lname varchar(20),
	ext numeric(3)
);
 
CREATE TABLE THistPlop (
	datum datetime PRIMARY KEY,
	id numeric(2),
	fname varchar(20),
	lname varchar(20),
	ext numeric(3)
);
 
create trigger plopupd
on tplop
for update
as
begin
/*	if ( update(fname) or update(lname) )*/
	IF ( ((COLUMNS_UPDATED() & 2) > 0) or ((COLUMNS_UPDATED() & 4) > 0) )
	begin
		insert into THistPlop SELECT (current_timestamp), * FROM Inserted
	end
end
 
 
insert into tplop values (9,'i','i',9)
insert into tplop values (10,'j','j',10)
insert into tplop values (11,'k','k',11)
 
/* Ne devrait pas faire d'ajout dans l'historique et pourtant oui :(*/
update tplop set fname='i', lname='i', ext=9 where id = 9

08/04/2004, 13h18
Google.be
Yo cousin !

Je pense a un petit truc pour ton code

Code:

update tplop set fname='i', lname='i', ext=9 where id = 9

C'est pas un peu normal qu'il écrive dans l'historique même si t'a rien changé ? Paske en fait, le dbms il s'en tape royalement que tu remette les meme valeurs. Pour lui, t'a changé des choses donc ---> trigger on update est déclanché !

Je pense que tu dois vérifier si les champs on VRAIMENT changé (sémantiquement si je puis dire), genre :
Code:

1 2 3 4 IF (old_version != new_version) THEN update = true; ... ... IF ( update) THEN .... /* ton update ici dans la table historique */
Je me trompe ou pas ?
08/04/2004, 13h20
Wakko2k

bah c'est ca le prob, le comportement qu'on attend de ce genre de brol c'est qu'il fasse la comparaison tout seul pour que lui detecte si il y a vraiment modif ou pas...
08/04/2004, 13h23
Google.be
Ce que tu peut faire c'est de faire ton update que sur les colonnes qui ont changé..

En gros, faire la vérification avant de lancer ton query. La normalement, le trigger fera l'effet voulu.

Si tu fait :
Code:

1 2 3 /* Pas de modification d'un champ dont tu test le changement dans ton trigger */ UPDATE tplop SET lname='bidon' where id = 9
Est-ce que ton trigger fait bien son boulot ?
08/04/2004, 13h29
Wakko2k

il fait bien son boulot tant que je le modifie dans la representation "grille" de sql a la main, la il marche nickel

bah alors je reporte la gestion de l'historique sur le client et c'est plutot degueu sachant que c'est pas son boulot a la base de gerer ce genre de bazar
08/04/2004, 13h34
Google.be

Ben si c'est le client qui crée le query SQL (l'insert), c'est quand meme a lui de le faire, je 'bien' le construire, afin de ne mettre de SET seulement pour les champs qui ont changé...

C'est clair que c'est pas le top du top mais bon, fo bien que ca marche, tant pis si c'est pas super beau :lol:
08/04/2004, 14h00
Wakko2k

bah sinon je peux utiliser une proc stockée qui va faire l'update ou non selon le cas mais c'est vraiment debile etant donne que c'est qd mm un peu le boulot du trigger

et pour repondre a une autre question, non on ne peut pas recuperer les valeurs d'avant l'update car en sql server c'est un after update et il n'y a pas de before update, et comme precisez avant, si je fais un instead update je tombe dans un autre prob qui est :

l'instead update va selon si oui ou non vrai modif sur mes champs il y a, ajouter un tuple dans la table d'historique mais il faut qu'il update aussi la table TPlop en elle mm et donc, ca va reappeler l'instead update qui va reappeler l'instead etc etc...

sinon bah ce sera le client qui ajoute direct dans l'historique mais encore une fois, je vais jouer le puriste, c'est pas son boulot du tout

tout ca pcq if(update) ou if (column_update) à la place de vraiment regarder si il y a un update de valeur, regarde slmt dans le query d'update si oui ou non le nom du champ est specifie, si des gens utilisent Postgres, Mysql, etc... je suis bien curieux de voir si eux aussi reagissetn comme ca

MERCI LOUIS :)
En fait dans un update on peut avoir acces aux anciennes valeurs via la table Deleted

Dans ma recherche j'avasi trouvé ca aussi pour ceux qui ont des probs d'update qui se declenche alors qu'aucune ligne n'est modifie ici : http://support.microsoft.com/default.aspx?scid=kb;en-us;64238
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 create trigger plopupd on tplop for update as declare @fname varchar (20), @lname varchar (20), @oldfname varchar (20), @oldlname varchar (20) SELECT @fname = I.fname FROM Inserted I SELECT @lname = I.lname FROM Inserted I SELECT @oldfname = D.fname FROM Deleted D SELECT @oldlname = D.lname FROM Deleted D begin if ((@oldfname <> @fname) OR (@oldlname <> @lname)) begin insert into THistPlop SELECT (current_timestamp), * FROM Inserted end end