Création d'une table historique par trigger

**zemblamoh** · 22/10/2009, 11h39

Bonjour,
J'ai une table (Table1) avec des colonnes Col1,col2.... Je souhaite voir s'insérer dans une table historiques (Historic_Table1), les lignes qui sont supprimées sur la Table1. J'ai pensé qu'un trigger pourrait m'aider à développer ce problème, mais je ne sais pas comment procéder. Je cherche de l'aide ou un tutorial qui va dans ce sens.
Merci de votre aide

**elsuket** · 22/10/2009, 12h09

Bonjour,

Tout d'abord il vous faut créer une table qui a la même structure que votre table source, avec en plus une colonne de type DATETIME qui stockera la date de suppression des lignes de la table source :

Code :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE dbo.Historic_Table1
(
	col1_Table1,
	col2_Table1,
	colN_Table1,
	...
	colX_Table2,
	dateSuppression DATETIME NOT NULL CONSTRAINT DF_Historic_Table1_dateSuppression DEFAULT (GETDATE())
)

Et voici le 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
CREATE TRIGGER TR_A_D_Table1
	ON dbo.Table1
	AFTER DELETE
AS
BEGIN
	-- pour ne pas retourner à l'application le
	-- nombre de lignes affectées
	SET NOCOUNT ON
 
	-- Traitement
	INSERT INTO dbo.Historic_Table1
	(
		col1,
		col2,
		colN,
		...
		colX
	)
	SELECT col1,
			col2,
			colN,
			...
			colX
	FROM DELETED
END

Dans les triggers, vous avez accès aux tables virtuelles INSERTED et DELETED, qui ont strictement la même structure que la table qui subit la modification de données :

- dans un trigger de type AFTER ou INSTEAD OF INSERT, seule la table virtuelle INSERTED est disponible. Celle-ci contient les lignes qui viennent d'être insérées par la transaction qui a déclenché le trigger,

- dans un trigger de type AFTER ou INSTEAD OF UPDATE, les tables virtuelles INSERTED et DELETED sont disponibles : INSERTED contient les nouvelles valeurs des lignes affectées par la transaction de modification, tandis que DELETED contient les anciennes valeurs de ces mêmes lignes.

- enfin dans un trigger de type AFTER ou INSTEAD OF DELETE, seule la table virtuelle DELETED est disponible. Celle-ci contient les lignes qui viennent d'être supprimées par la transaction qui a déclenché le trigger.

Pour aller plus loin avec les triggers, voyez ici et ici

Notez enfin que si vous êtes sous SQL Server 2008, vous pouvez utiliser la fonctionnalité de capture de changement de données

@++

**zemblamoh** · 22/10/2009, 13h08

Elsuket,
C'est exactement ce que je cherchai, ça marche à merveille.
merci beaucoup et surtout pour la rapidité de ta réponse.

**zemblamoh** · 22/10/2009, 19h17

Bonjour,
Mon problème de la création d'une table historique avec trigger est résolu, sauf pour un point. Quand j'enclenche un trigger pour l'update avec inserted..Je réalise que dans ma table historique, le trigger a envoyé la ligne où s'est positionné le curseur. Comment peut on annuler l'action du curseur??
Merci encore pour toute réponse.

**elsuket** · 22/10/2009, 19h29

Auriez-vous fait la terrible erreur de mettre un curseur dans votre curseur ?
En soi utiliser des curseurs dans du code SQL est une erreur, comme je l'ai démontré ici

Dans les deux cas, donnez-nous votre code, que nous le transformions en vrai code SQL : ensembliste !
SQL est en effet un langage conçu pour traiter les données dans leur ensemble, et non pas tuple par tuple, comme on le fait dans tout autre type de programmation. Donc, banissez les curseurs.
SQL est enfin un langage délcaratif : vous ne devez pas imposer, dans votre code, de méthode de traitement des données : le moteur le fait bien mieux que vous

@++

**verbal34** · 23/10/2009, 10h04

Envoyé par elsuket

Auriez-vous fait la terrible erreur de mettre un curseur dans votre curseur ?
En soi utiliser des curseurs dans du code SQL est une erreur, comme je l'ai démontré ici

Bonjour elsuket,

Après avoir jeté un coup d'oeuil sur le lien que vous avez communiquez, effectivement il n'y a pas photo!

Me concernant j'utilise un curseur pour faire un UPDATE d'une base de données de stats.

J'extrais des données depuis MabaseProd.dbo.Table1 INNER JOIN MabaseProd.dbo.Table2, selon un certain nombre de critères pour mettre à jour une base de stats MaBaseStat.dbo.MaTable (Une seule Table Cible).

en gros mon code ressemble à

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
 
USE MaBaseStat
GO
DECLARE @col1 INT
DECLARE @col2 VARCHAR(50)
DECLARE @col3 BIT
DECLARE cur_Today CURSOR FOR
SELECT MaTable1.Colonne1, MaTable.Colonne2, MaTable2.Colonne3
FROM MabaseProd.dbo.Table1 INNER JOIN MabaseProd.dbo.Table2 ON (bla bla bla)
WHERE (bla bla bla)
 
OPEN cur_Today
FETCH cur_Today INTO @col1, @col2, @col3
WHILE @@Fetch_Status = 0
BEGIN
	UPDATE MaTable
	SET		Colonne2 = @col2,
			Colonne3 = @col3
	WHERE	Colonne1 = @col1
FETCH cur_Today INTO @col1, @col2, @col3
END
CLOSE cur_Today
DEALLOCATE cur_Today

j'aimerais bien savoir comment le transformer sans utiliser de curseur?

Actuellement ça fonctionne à merveille car je n'ai que quelques milliers de lignes à mettre à jour mais j'appréhende le jour où ça sera 10 voir 100 fois plus

Merci d'avance.

**zemblamoh** · 23/10/2009, 10h44

Bonjour Elsuket,
Peut être devrai je m'expliquer mieux. J'accède à mes tables par l'intermédiaire de formulaires en projet access (*.adp). J'ai remarqué, une fois mon trgigger UPDATE installé, que lorsque j'ouvre la formulaire de la table1 conernée, le trigger affiche dans la table d'insertion (historique_table1), la ligne ou le curseur est placé. Je ne vois pas dans ces conditions comment bannir le curseur dans mon cas.
Mon code est simple, inspiré du votre:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
ALTER TRIGGER TabSurvey_Trigger2
ON dbo.TabSurvey
AFTER UPDATE
AS
BEGIN
	SET NOCOUNT ON
	------
	INSERT INTO DBO.TabSurvey_Histo_U(Hole_Id,anc_id,at,brg,dip)
	SELECT Hole_Id,anc_id,at,brg,dip
	FROM INSERTED
END

Merci encore et A+

**SQLpro** · 23/10/2009, 11h58

Vous ne nous aidez pas la tâche en mettant bla blla bla.... Mais au final pas boesoin d'aucun curseur car la commande SQL se résume à :

Code :

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

1
2
3
4
5
6
7
8
	UPDATE MaTable
	SET		Colonne2 = T2.Colonne2,
			Colonne3 = T2.Colonne3
        FROM    MabaseProd.dbo.Table1 AS T1 
            INNER JOIN MabaseProd.dbo.Table2 AS T2
                  ON (bla bla bla)
	WHERE	(bla bla bla)
        AND   Colonne1 = T1.Colonne1

Bref, apprenez le SQL.... Mon livre comme mon site web peuvent vous y aider !

A +

**verbal34** · 23/10/2009, 12h27

Envoyé par SQLpro

Vous ne nous aidez pas la tâche en mettant bla blla bla....

j'ai mis les bla bla vu que les clauses de jointure et dans le where n'avaient pas d'importance (je pense) dans ce contexte

c'était juste une requête d'exemple et j'aurais du effectivement faire un peu plus d'effort en postant :

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
 
SELECT MaTable1.Colonne1, MaTable.Colonne2, MaTable2.Colonne3
FROM MabaseProd.dbo.Table1 INNER JOIN MabaseProd.dbo.Table2 
ON (MabaseProd.dbo.Table1.Colonne1 = MabaseProd.dbo.Table2.Colonne4)
WHERE (MaTable2.Colonne3 = Valeur)
 
OPEN cur_Today
FETCH cur_Today INTO @col1, @col2, @col3
WHILE @@Fetch_Status = 0
BEGIN
	UPDATE MaTable
	SET		Colonne2 = @col2,
			Colonne3 = @col3
	WHERE	Colonne1 = @col1
FETCH cur_Today INTO @col1, @col2, @col3
END
CLOSE cur_Today
DEALLOCATE cur_Today

Envoyé par SQLpro

Bref, apprenez le SQL.... Mon livre comme mon site web peuvent vous y aider !

C'est ce que j'essaye de faire en postant sur ce forum et en lisant aussi vos cours sur votre site

En tout cas merci pour votre réponse.

**zemblamoh** · 23/10/2009, 12h47

A SQLPro,
Ce que vous proposez à Verbal34, pourrait il résoudre mon problème?
Moi je veux juste insérer dans une table historique les lignes d'une table précise où il y a eu intervention (update). Pouvez vous me proposer une solution?
Merci beaucoup

**elsuket** · 24/10/2009, 13h48

Envoyé par Zemblamoh

Je ne vois pas dans ces conditions comment bannir le curseur dans mon cas.

De quel curseur parlez-vous ? Sans votre code, il nous sera difficile de vous répondre précisément

Votre trigger semble être correct, mais nous n'avons pas la structure de vos table non plus !

@++

**zemblamoh** · 28/10/2009, 17h46

Bonjour Elsuket,
Effectivement le trigger est juste. Je pense que cette erreur provient du faite que ma table est elle même issue d'une requête selection et qu'à la fin j'y accède par un formulaire (projet adp) où j'oriente mon curseur avec docmd.gotorecord... C'est un peu tordu, mais à la fin j'ai trouvé une autre solution. Je passe, avec sql, par la crétion de tables provisoires et par comparaison à créer une table Historique avec les lignes modifiées et suprimées tout en ajoutant getdate() et aussi le nom du client intervenant.
En tout cas j'ai appris pleins de choses sur les triggers et sur d'autres formes relatives à l'usage sql.
Merci beaucoup et A+

**mohamed Benaoua** · 19/09/2017, 13h43

j'ai créer une table pour l'historique de suppression, quand tu supprime une ligne ca marche , mais quand tu vas supprimer une deuxième ligne un message apparait que la table existe pourtant qu'on a fait q'une insertion de donnés a partir deleted

**iberserk** · 21/09/2017, 12h55

Envoyé par mohamed Benaoua

j'ai créer une table pour l'historique de suppression, quand tu supprime une ligne ca marche , mais quand tu vas supprimer une deuxième ligne un message apparait que la table existe pourtant qu'on a fait q'une insertion de donnés a partir deleted

Vous déterrez un topic vieux de .... 8 ans?

je suppute que vous ayez mis le code de création de la table d'historique directement dans votre TRIGGER!!!

Création d'une table historique par trigger

Développement SQL Server

Discussions similaires

Partager

Partager