Trigger on DELETE

Version imprimable

Trigger on DELETE

Bonjour,

Je souhaite créer un trigger ON delete sur une table pour permettre de recupérer la ligne supprimée et de faire un INSERT INTO sur une autre table (un espèce d'archivage de table A vers A_archive ;))

Le trigger est déjà crée :
Code:

1 2 3 4 5 6 7 CREATE TRIGGER TRIGGER_on_delete_A ON A FOR DELETE AS INSERT INTO A_archive (numero, container, date_heure, badge) SELECT numero, container, date_heure, badge FROM A
Mais cela ne fonctionne pas, pourtant il est bien présent.
Vérifier avec cette requête :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 SELECT TR.name AS nomTrigger, T.name AS nomTable, TR.is_disabled AS estActif, CASE TR.is_instead_of_trigger WHEN 1 THEN 'INSTEAD OF' ELSE 'AFTER' END AS typeDeclenchement, TRE.type_desc AS DMLDeclencheur, TRE.is_first AS estPremier, TRE.is_last AS estDernier FROM sys.triggers AS TR JOIN sys.tables AS T ON TR.parent_id = T.object_id JOIN sys.trigger_events AS TRE ON TR.object_id = TRE.object_id ORDER BY T.name, TR.name
Est-ce qu'il n'y a pas une option sur le type de déclenchement BEFORE ou AFTER?
J'ai loupé une étape sur la création?

Merci d'avance :)

21/11/2013, 16h43
Lyche
Bonjour,

Si le DELETE est fait lors d'une procédure stockée, il est possible, via la clause OUTPUT, de faire un archivage immédiat des données supprimées.
Code:

1 2 3 4 5 DELETE dbo.maTableDelete OUTPUT DELETED.*, GETDATE() INTO dbo.maTableHisto(champs*, date_histo) WHERE ...
Et pour votre question, sous SQL Server c'est une fonction INSTEAD OF. Où l'on remplace le comportement attendu (ici un DELETE) par le trigger.

Edit : Je vous invite à consulter les tables de session INSERTED et DELETED, elles pourront résoudre votre soucis d'accès à l'information supprimée ;)

Cordialement,
Lyche
21/11/2013, 16h44
aieeeuuuuu

Bonjour,

Que veux dire "ne fonctionne pas" ? comment testez vous son fonctionnement ? quel est le résultat ?...

Tel que vous faites votre trigger, il va insérer la totalité de la table A, pas uniquement la ligne supprimée. Pour cela, il faudrait utiliser la pseudo-table INSERTED

EDIT : la table DELETED dans votre cas
21/11/2013, 17h34
afrodje

Citation:

Envoyé par Lyche

Bonjour,

Si le DELETE est fait lors d'une procédure stockée, il est possible, via la clause OUTPUT, de faire un archivage immédiat des données supprimées.

Non ce n'est pas le cas, c'est une application tierce qui supprime les lignes directement avec un DELETE

Citation:

Envoyé par aieeeuuuuu

Que veux dire "ne fonctionne pas" ? comment testez vous son fonctionnement ? quel est le résultat ?...

Je test le fonctionnement en faisant un DELETE sur A en SQL pour voir si la ligne passe de A à A_archive

Citation:

Envoyé par aieeeuuuuu

Tel que vous faites votre trigger, il va insérer la totalité de la table A, pas uniquement la ligne supprimée. Pour cela, il faudrait utiliser la pseudo-table INSERTED

Effectivement.
Je vais me baser sur des exemples (ex : http://www.developpez.net/forums/d92...eted-inserted/

Code:

1
2
3
4
5
6
7
CREATE TRIGGER TRIGGER_on_delete_A
ON A
FOR DELETE
AS
     INSERT INTO A_archive  (numero, container, date_heure, badge)
     SELECT numero, container, date_heure, badge
     FROM deleted

A +

Citation:
Envoyé par SQLpro
Code:

1 2 3 4 5 6 7 CREATE TRIGGER TRIGGER_on_delete_A ON A FOR DELETE AS INSERT INTO A_archive (numero, container, date_heure, badge) SELECT numero, container, date_heure, badge FROM deleted
A +
Presque parfait :ccool:
Je me suis permis de modifier ta requête
Code:

1 2 3 4 5 6 7 8 CREATE TRIGGER TRIGGER_on_delete_A ON A FOR DELETE AS INSERT INTO A_archive (numero, container, date_heure, badge) SELECT a.numero, a.container, a.date_heure, a.badge FROM deleted as A INNER JOIN DELETED AS D ON A.id = D.id
Le problème avec ta requête est en supprimant seulement une ligne, le trigger insérait toutes les lignes dans A_archive.
Avec la jointure, il insère uniquement la ligne supprimée.

Merci à vous tous :mrgreen:

22/11/2013, 11h18
aieeeuuuuu

Citation:

Envoyé par afrodje

Le problème avec ta requête est en supprimant seulement une ligne, le trigger insérait toutes les lignes dans A_archive.

Heu... êtes-vous sûr de ça ???

Quelque chose m'échappe, car la requete de SQLpro me semble correcte, et celle que vous proposez en correction me semble... disons... étrange

Est-ce que votre instance est sensible à la casse (pour les noms d'objet) et auriez vous une table nommée "deleted", comportant les même colonnes que votre table A (issue d'un ancien test peut-etre ?)
22/11/2013, 12h31
afrodje

Citation:

Envoyé par aieeeuuuuu

Heu... êtes-vous sûr de ça ???

Quelque chose m'échappe, car la requete de SQL me semble correcte, et celle que vous proposez en correction me semble... disons... étrange

Est-ce que votre instance est sensible à la casse (pour les noms d'objet) et auriez vous une table nommée "deleted", comportant les même colonnes que votre table A (issue d'un ancien test peut-etre ?)

J'ai testé votre requête, c'est le résultat de mes tests, à plusieurs reprises.

J'ai aussi testé à plusieurs reprise ma requête et cela fonctionne :roll:

Non aucune table nommé "deleted" pourtant...

Aucun doute que votre requete fonctionne, mais l'auto-jointure est parfaitement inutile.

Le trigger proposé par SQLPro est bien correct :

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
 
--création et peuplement des tables
 
CREATE TABLE A (
		numero		INT 
	,	container	INT 
	,	date_heure	DATETIME2
	,	badge		INT
)
GO
 
CREATE TABLE A_archive (
		numero		INT 
	,	container	INT 
	,	date_heure	DATETIME2
	,	badge		INT
)
GO
 
INSERT INTO A (
		numero		
	,	container	
	,	date_heure	
	,	badge		
) VALUES
	(1,1,GETDATE(), 1)
,	(2,2,GETDATE(), 2)
,	(3,3,GETDATE(), 3)
 
GO

Code:

1
2
3
4
5
6
7
8
9
10
 
--creation du déclencheur 
 
CREATE TRIGGER TRIGGER_on_delete_A
ON A
FOR DELETE
AS
     INSERT INTO A_archive  (numero, container, date_heure, badge)
     SELECT numero, container, date_heure, badge
     FROM deleted

Code:

1
2
3
4
5
6
 
--test
SELECT * FROM A;
DELETE FROM A WHERE numero =2;
SELECT * FROM A;
SELECT * FROM A_archive;

résultat :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
numero      container   date_heure             badge
----------- ----------- ---------------------- -----------
1           1           2013-11-22 16:30:42.65 1
2           2           2013-11-22 16:30:42.65 2
3           3           2013-11-22 16:30:42.65 3
 
 
numero      container   date_heure             badge
----------- ----------- ---------------------- -----------
1           1           2013-11-22 16:30:42.65 1
3           3           2013-11-22 16:30:42.65 3
 
 
numero      container   date_heure             badge
----------- ----------- ---------------------- -----------
2           2           2013-11-22 16:30:42.65 2