Discussion :

[afrodje]

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 :

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

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 :

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
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

[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 sql :

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

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

[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

[afrodje]

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

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

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/

[SQLpro]

Code :

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

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 +

[afrodje]

Code :

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

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
Je me suis permis de modifier ta requête

Code :

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

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

[aieeeuuuuu]

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 ?)

[afrodje]

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

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

[aieeeuuuuu]

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

Le trigger proposé par SQLPro est bien correct :

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
 
--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 :

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

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 :

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

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 :

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
 
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