Trigger pour historisation de données

**actionthomas** · 10/03/2010, 14h25

Bonjour

Afin de pouvoir gérer l'historique de ma base à chaque changement, j'ai pensé que des triggers pouvaient m'aider.
C'est mon premier contact avec ces bestioles là, et autant dire tout de suite que je suis pas une star en T-SQL

Pour schématiser, voici le mécanisme que j'aimerais mettre en oeuvre :

2 tables, une parent et une enfant, qui ont chacune une colonne de données (c'est pour simplifier), et l'horodatage.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
CREATE TABLE dbo.Parent
	(
	idParent uniqueidentifier NOT NULL,
	ValeurParent int NOT NULL,
	DateDebut datetime NOT NULL,
	DateFin datetime NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Parent ADD CONSTRAINT
	DF_Parent_DateDebut DEFAULT getdate() FOR DateDebut
GO
ALTER TABLE dbo.Parent ADD CONSTRAINT
	PK_Parent PRIMARY KEY CLUSTERED 
	(
	idParent
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 
GO
ALTER TABLE dbo.Parent SET (LOCK_ESCALATION = TABLE)
GO
CREATE TABLE dbo.Enfant
	(
	idEnfant uniqueidentifier NOT NULL,
	idParent_FK uniqueidentifier NOT NULL,
	ValeurEnfant int NOT NULL,
	DateFin datetime NULL,
	DateDebut datetime NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Enfant ADD CONSTRAINT
	DF_Enfant_DateDebut DEFAULT getdate() FOR DateDebut
GO
ALTER TABLE dbo.Enfant ADD CONSTRAINT
	PK_Enfant PRIMARY KEY CLUSTERED 
	(
	idEnfant
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 
GO
ALTER TABLE dbo.Enfant ADD CONSTRAINT
	FK_Enfant_Parent FOREIGN KEY
	(
	idParent_FK
	) REFERENCES dbo.Parent
	(
	idParent
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
 
GO
ALTER TABLE dbo.Enfant ADD CONSTRAINT
	FK_Enfant_PetitEnfant FOREIGN KEY
	(
	idEnfant
	) REFERENCES dbo.PetitEnfant
	(
	idPetitEnfant
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
 
GO

Lorsque ValeurParent change, voici ce que j'ai voulu implémenter dans mon trigger :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
DECLARE @NewID as uniqueidentifier
SET @NewID = NewID()
 
-- On remet les anciennes valeurs dans la ligne mise à jour
UPDATE Parent
SET DateFin = GETDATE(),
      ValeurParent = Deleted.ValeurParent
FROM Parent, Deleted
WHERE Parent.idParent = Deleted.idParent
 
-- On crée la ligne avec les valeurs à jour
INSERT INTO Parent
(idParent, ValeurParent, DateDebut)
SELECT
	@NewID, 
	Inserted.ValeurParent,
	GETDATE()
FROM Inserted
 
-- Et on pousse la mise à jour vers les enfants 
UPDATE Enfant
SET idParent_fk = @NewID
FROM Enfant, Deleted
WHERE Enfant.idParent_FK = Deleted.idParent
AND Enfant.DateEnd IS NULL

Jusque là, tout va bien.
Mais du coup, comme le parent a changé, il faut changer les enfants aussi.
Et donc, un autre Trigger, sur la table enfant cette fois.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DECLARE @NewID as uniqueidentifier
SET @NewID = NewID()
 
-- On remet les anciennes valeurs dans la ligne mise à jour
UPDATE Enfant
SET DateFin = GETDATE(),
      ValeurEnfant = Deleted.ValeurEnfant,
      idParent_FK = Deleted.idParent_FK
FROM Enfant, Deleted
WHERE Enfant.idEnfant = Deleted.idEnfant
 
-- On crée la ligne avec les valeurs à jour
INSERT INTO Enfant
(idEnfant, idParent_FK, ValeurEnfant, DateDebut)
SELECT
	@NewID, 
             Inserted.idParent_FK,
	Inserted.ValeurParent,
	GETDATE()
FROM Inserted

Et ca marche très bien... Tant qu'il y a qu'un enfant à modifier !
J'ai lu qu'il fallait traiter les triggers de manière ensembliste, et ne pas utiliser de variable locale dedans.
Le problème c'est le NewID() de l'enfant, je ne vois pas comment créer un nouveau GUID pour chaque ligne de la table Enfant qui va être recréée depuis le Trigger.

Merci pour votre aide