TRIGGER - DELETE dans FOR UPDATE ne passe pas dans INSTEAD OF DELETE

[stephane.julien]

Bonjour,

j'ai une table avec deux triggers :

1. TRIGGER FOR UPDATE qui supprime éventuellement des tuples de sa table en fonction de leurs états
2. TRIGGER INSTEAD OF DELETE qui réécrit la cascade

Lors du DELETE qui figure dans mon premier TRIGGER, le deuxième n'est pas appelé, donc ma réécriture de cascade ne fonctionne pas, donc ma suppression non plus. A part passer par une procédure stockée, y a-t-il une solution (configuration ou autre ?? ). Je n'aimerais pas passer par la procédure stockée car il faudrait faire un curseur pour parcourir les tuples du TRIGGER ensembliste, et c'est trop lent.

Merci d'avance de votre aide !

[stephane.julien]

J'ai tenté d'activer la récursivité des TRIGGERS, toujours pas bon.

Code :

SET RECURSIVE_TRIGGERS ON | OFF

[elsuket]

Bonjour,

Sans le code, difficile de vous aider ...

@++

[stephane.julien]

L'option NESTED TRIGGERS est également activée...

[stephane.julien]

Voici ma table avec les deux TRIGGERS. C'est une table de test, elle n'est pas très propre.

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
CREATE TABLE [dbo].[Test2] (
  [Id] int NULL,
  [iValue] int NULL
)
ON [PRIMARY]
GO
 
CREATE TRIGGER [dbo].[Test2_tru] ON [dbo].[Test2]
WITH EXECUTE AS CALLER
FOR UPDATE
AS
BEGIN
  DELETE FROM Test2
END
GO
 
CREATE TRIGGER [dbo].[Test2_trd] ON [dbo].[Test2]
WITH EXECUTE AS CALLER
INSTEAD OF DELETE
AS
BEGIN
  INSERT INTO Test SELECT COUNT(Id), 'toto', COUNT(Id) FROM DELETED
END
GO

Si le TRIGGER INSTEAD OF DELETE était appelé, les tuples ne devraient pas se supprimer, et je devrais trouver des données dans mon autre table "Test", ce qui n'est pas le cas.

[SQLpro]

D'abord il est inutile de faire un curseur comme vous le suggérer, ensuiste tout dépend du code de vos trigger.
postez les !

En effet il n'y a pas de raison que cela marche mal... Démonstration :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE DATABASE DB_TEST_TRIGGER;
GO
 
USE DB_TEST_TRIGGER;
GO
 
CREATE TABLE T1 
(T1_ID    INT NOT NULL IDENTITY PRIMARY KEY,
 T1_DATA  VARCHAR(32));
GO 
 
CREATE TABLE T2 
(T2_ID    INT NOT NULL IDENTITY PRIMARY KEY,
 T2_DATA  VARCHAR(32));
GO

Le déclencheur UPDATE de T1

Code :

1
2
3
4
5
6
CREATE TRIGGER E1
ON T1
FOR UPDATE
AS
    DELETE T2;
GO

le déclencheur INSTAED OF DELETE de T2

Code :

1
2
3
4
5
6
7
CREATE TRIGGER E2
ON T2
INSTEAD OF DELETE
AS
   UPDATE T2
   SET T2_DATA = UPPER(T2_DATA)
GO

Les données de test :

Code :

1
2
3
4
5
INSERT INTO T1 VALUES ('UN UN UN UN ');
INSERT INTO T1 VALUES ('DEUX DEUX ');
 
INSERT INTO T2 VALUES ('un un un un ');
INSERT INTO T2 VALUES ('deux deux ');

On lance un update sur T1 :

Code :

1
2
UPDATE T1
SET T1_DATA = UPPER(T1_DATA);

Résultat de T2

Code :

1
2
3
4
5
6
SELECT * FROM T2
 
T2_ID       T2_DATA
----------- --------------------------------
1           UN UN UN UN 
2           DEUX DEUX .

La majusculisation a été effectuée !

A +

[stephane.julien]

Le code de mes TRIGGERS est assez long. C'est pourquoi j'ai fait un exemple succin illustrant mon problème. Le code que j'ai posté ne fonctionne pas chez moi. Ensuite, la solution CURSOR serait envisagée si je devais passer par les procédures stockées. Si pas de SP, pas de CURSOR. C'est clair pour moi.

La différence entre mon code et le votre se trouve dans le TRIGGER FOR UPDATE. Je DELETE des tuples de la même table, vous supprimez des tuples d'une autre table. Les TRIGGERS FOR UPDATE et INSTEAD OF DELETE sont sur la même table ! Je pense que c'est la source du problème.

[elsuket]

Si pas de SP, pas de CURSOR. C'est clair pour moi.

C'est faux quand même.
Si cela n'est pas possible, c'est que votre conception est fausse.

Votre second trigger doit être un AFTER et pas un INSTEAD OF

@++

[stephane.julien]

Je ne peux malheureusement pas le mettre en AFTER DELETE. J'ai 4 tables sur lesquelles j'ai été obligé à réécrire la cascade en INSTEAD OF DELETE. Les deux tables du fond de la cascade doivent être archivées avant suppression. Si je fait du AFTER DELETE, je perds les référence entre les tuples DELETED de mes 4 tables en cascade. La cascade est pioritaire sur les TRIGGERS...

J'ai donc mes 4 tables qui ont un INSTEAD OF DELETE, et j'ai besoin d'un FOR UPDATE ou AFTER UPDATE sur l'une d'entre elles...

Etant donnée qu'on ne peut pas passer des tables aux procédures stockées, je ne vois pas d'autres solutions que de faire un curseur si la suppression devait se faire dans une procédure stockée. Mes deux triggers feraient alors appel à la même procédure stockée.. bof bof...

[SQLpro]

Encore une fois, vous n'avez sans doute pas besoin de curseur dans votre PS.

A +

[stephane.julien]

Je pense que vous avez raison. Par contre je ne vois pas comment faire.

Si j'avais du le faire par SP, j'aurais fait un curseur dans mon trigger qui aurait bouclé, et appelé autant de fois ma SP qu'il n'y a de tuples dans ma table DELETED.

Comment feriez-vous pour traiter tous les tuples qui sont dans ma table DELETED dans une procédure stockée ?

[elsuket]

Ce que je ne parviens pas à comprendre c'est pourquoi le trigger INSTEAD OF ne se déclenche pas sur le DELETE du trigger AFTER, alors que si on transforme le trigger INSTEAD OF en AFTER, tout se passe comme prévu.

Je n'ai rien trouvé dans les notes de la documentation, donc je cherche toujours une explication ...

@++

[SQLpro]

Envoyé par stephane.julien

Je pense que vous avez raison. Par contre je ne vois pas comment faire.

Si j'avais du le faire par SP, j'aurais fait un curseur dans mon trigger qui aurait bouclé, et appelé autant de fois ma SP qu'il n'y a de tuples dans ma table DELETED.

Comment feriez-vous pour traiter tous les tuples qui sont dans ma table DELETED dans une procédure stockée ?

2 façons :
1) le plus complexe, mais la plus performante est de réintégrer le code de la SP dans le trigger en utilisant notamment les CTE
2) avec une variable table dans laquelle on place les clef des lignes à traiter avec en sus une colonne DEL de type BIT à O, on fait un WHILE EXISTS... qui va récupérer une clef, lance la proc et UPDATE la table variable...

A lire : http://sqlpro.developpez.com/cours/s...r_avoidCursor/

A +

[SQLpro]

Envoyé par elsuket

Ce que je ne parviens pas à comprendre c'est pourquoi le trigger INSTEAD OF ne se déclenche pas sur le DELETE du trigger AFTER, alors que si on transforme le trigger INSTEAD OF en AFTER, tout se passe comme prévu.

Je n'ai rien trouvé dans les notes de la documentation, donc je cherche toujours une explication ...

@++

De mémoire, c'est normal. Les trigger INSTEAD étant un substitue de transaction, on ne peut substituer une transaction à une transaction déjà ouverte. PEut être un COMMIT préalable dans le premier trigger réglera l'affaire !!!!

A +

[aieeeuuuuu]

Envoyé par SQLpro

PEut être un COMMIT préalable dans le premier trigger réglera l'affaire !!!!

==>

Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

Je suis comme Elsuket : dubitatif. La doc n'indique pas grand chose la dessus...

[SQLpro]

De toute façon ce que stéphane julien essaye de faire, c'est de la programmation spaghetti événementielle... Bref, une grosse m..... (je dis pas le mot, car on va encore me tomber dessus !!!)

S'il nous disait ce qu'il veut faire au point de vu fonctionnel on pourrait sans doute l'orienter...

A +

[elsuket]

D'accord, mais en supposant que notre modèle soit correct et l'on n'ait pas d'autre choix que d'implémenter ces deux triggers, que faire ?

@++

[stephane.julien]

Tout d'abord, merci pour vos réponses et votre rapidité !

C'est peut-être là que j'ai un problème de conception, mais je ne vois vraiment pas comment faire autrement. Peut-être pouvez-vous m'aider à démêler mon plat de Spaghettis

Envoyé par stephane.julien

J'ai 4 tables sur lesquelles j'ai été obligé à réécrire la cascade en INSTEAD OF DELETE. Les deux tables du fond de la cascade doivent être archivées avant suppression. Si je fait du AFTER DELETE, je perds les référence entre les tuples DELETED de mes 4 tables en cascade. La cascade est pioritaire sur les TRIGGERS...

J'ai donc mes 4 tables qui ont un INSTEAD OF DELETE, et j'ai besoin d'un FOR UPDATE ou AFTER UPDATE sur l'une d'entre elles...

Voici un schéma illustrant mon problème : model.png
Et une DB qui fait ce dont j'ai besoin : dump.sql

Je ne peux malheureusement pas envoyer ma base complète avec 80 tables et des données confidentielles.

Si je supprime un tuple de la table B, l'archivage de C et C_Data fonctionne bien. Par contre si j'update son champ Status_Id à 2, le TRIGGER B_tru est déclenché, il me fait un DELETE, et je ne passe pas dans le TRIGGER B_trd.

Etant donné que mon TRIGGER B_trd (INSTEAD OF DELETE) est petit, je pourrais dupliquer le code dans le TRIGGER B_tru (AFTER UPDATE), mais c'est ce que j'essaie d'éviter. Ce serait la solution de secours...

Merci d'avance de votre aide !

[stephane.julien]

Envoyé par SQLpro

1) le plus complexe, mais la plus performante est de réintégrer le code de la SP dans le trigger en utilisant notamment les CTE
2) avec une variable table dans laquelle on place les clef des lignes à traiter avec en sus une colonne DEL de type BIT à O, on fait un WHILE EXISTS... qui va récupérer une clef, lance la proc et UPDATE la table variable...

Merci pour ces pistes. La solution 2) est beaucoup plus rapide que la solution CURSEUR j'imagine ?? J'ai éventuellement 1-2 curseurs à migrer..

[stephane.julien]

Salut !

Je suis à nouveau tombé dans ce cas de figure ailleurs dans ma base de données. Pour résoudre, j'écris le contenu de mon INSTEAD OF DELETE dans le AFTER UPDATE (3 lignes de code).

Ce n'est cependant pas la solution la plus élégante. Si quelqu'un trouve une solution pour que le TRIGGER INSTEAD OF DELETE soit appelé aussi depuis un AFTER UPDATE, je suis preneur...