Trigger de suppression SQL Server

[lamiae.ch]

Bonjour à Tous

Je cherche à developper un trigger de suppresssion pour supprimer un enregistrement d'une vue qui porte sur plusieurs tables .

est ce que vous pouvez m'aider !!!
j'ai cherché le format de trigger , et j'ai un petit code , mais ne je sais pas comment je peux executer le trigger , comment je peut passer l identifiant de la ligne à supprimer ?

Merci pour votre aide
et Bonne journée

[StringBuilder]

Déjà, avec SQL Server 2005 et ultérieurs (il me semble pas que ça marchait avec 2000), tu n'as pas forcément besoin de trigger sur une vue pour faire un DELETE.

Ensuite, si nécessité de trigger il y a, alors c'est simple :

Tu crées ton trigger "intead of delete" sur ta vue.
Et dans le trigger, tu vas supprimer dans les tables sur lesquelles pointe ta vue en fonction des données que tu trouveras dans la table virtuelle "deleted" à l'intérieur du trigger.

Ensuite, pour lancer le trigger, il faut faire "delete from tavue where ..."

Ensuite, dans le trigger, la table virtuelle "deleted" contiendra l'ensemble des lignes de la vue qui correspondent à la command DELETE que tu as tapé.

Tu peux boucler dessus (avec un curseur) ou t'en servir dans des sous-requête pour faire le delete dans les tables physiques.

C'est tout ce qu'on peut de dire avec si peu d'informations...

Modèle de donnée ? Code de la vue ? Traitement attendu de la part du trigger ?

[lamiae.ch]

En faite j'utilise SSMS 2008 R2 , le code de ma vue est le suivant :

SELECT p.codeProject, p.projectName, p.PlanID, f.FileID, f.version, e.controlType, e.label, e.typeValue, fev.value, p.ProjectID, fe.FileElementID, fe.ElementID,
fev.FileElementValueID, i.codeFiness, i.name AS InstitutionName, a.codeRegion, a.name AS ARSName, pl.name AS PlanNAme
FROM dbo.DRPI_Project AS p INNER JOIN
dbo.DRPI_Institution AS i ON p.InstitutionID = i.InstitutionID INNER JOIN
dbo.DRPI_Ars AS a ON i.ArsID = a.ArsID INNER JOIN
dbo.DRPI_Plan AS pl ON p.PlanID = pl.PlanID INNER JOIN
dbo.DRPI_File AS f ON p.ProjectID = f.ProjectID LEFT OUTER JOIN
dbo.DRPI_FileElement AS fe ON fe.FileID = f.FileID LEFT OUTER JOIN
dbo.DRPI_Element AS e ON fe.ElementID = e.ElementID LEFT OUTER JOIN
dbo.DRPI_FileElementValue AS fev ON fe.FileElementID = fev.fileElementID


et j'ai comme trigger de suppression de la vue , j 'ai utilisé ce code

ALTER TRIGGER [dbo].[declencheur_suppression]
ON [dbo].[DRPI_ProjectData]
INSTEAD OF DELETE
AS

Declare @codeprojet varchar(20),@nomprojet nvarchar(255), @PlanID int, @FileID int , @version int , @controlType varchar(20), @label varchar(255), @typeValue varchar(20), @value nvarchar(MAX), @ProjectID int,
@FileElementID int, @FileElementValueID int , @nameinstitution varchar (255), @codeRegion varchar(255), @nameARS varchar(255) , @Planname varchar (50)

BEGIN
SELECT @codeprojet = codeProject , @version = version , @label = label FROM deleted
Delete from DRPI_ProjectData where @codeprojet=codeProject


END


mais comme je ne suis pas professionnelle en T-SQL , j ai douté , pouviez vous me verifier le trigger !!

[StringBuilder]

Voici une illustration simpliste de mes dires.

Je me suis amusé ici à utiliser un trigger non pas pour supprimer des lignes dans les tables physiques, mais pour alimenter une table d'exclusion.

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
 
DROP VIEW v_test1;
DROP VIEW v_test2;
DROP TABLE exclude;
DROP TABLE test1;
 
CREATE TABLE test1
(
	id int NOT NULL identity PRIMARY KEY,
	filtre bit NOT NULL,
	val int NOT NULL
);
 
CREATE TABLE exclude (id int NOT NULL PRIMARY KEY REFERENCES test1(id));
 
INSERT INTO test1 (filtre, val) VALUES (0, 1);
INSERT INTO test1 (filtre, val) VALUES (1, 2);
INSERT INTO test1 (filtre, val) VALUES (1, 3);
INSERT INTO test1 (filtre, val) VALUES (0, 4);
INSERT INTO test1 (filtre, val) VALUES (1, 5);
INSERT INTO test1 (filtre, val) VALUES (0, 6);
go
 
CREATE VIEW v_test1 AS SELECT id, val FROM test1 WHERE filtre = 0;
go
 
CREATE VIEW v_test2 AS SELECT id, val FROM test1 WHERE id NOT IN (SELECT id FROM exclude);
go
 
SELECT * FROM v_test1;
 
DELETE v_test1 WHERE val > 5;
 
SELECT * FROM v_test1;
go
 
CREATE TRIGGER trg_test1 ON v_test2
instead of DELETE
AS
begin
	INSERT INTO exclude (id) SELECT id FROM deleted;
end;
go
 
SELECT * FROM v_test2;
 
DELETE v_test2 WHERE val > 3;
 
SELECT * FROM v_test2;
SELECT * FROM test1;
SELECT * FROM exclude;

[StringBuilder]

Votre trigger est presque bon, au détail près que si vous supprimez plusieurs lignes à la fois, ça va planter, vu que votre SELECT qui alimente vos variables va retourner plusieurs lignes.

Soit vous faites un "delete matbale where id in (select id from deleted)" (en ajoutant les jointures nécessaires) soit vous devez passer par un curseur.

[elsuket]

Bonjour,

Quand un trigger n'est pas ensembliste, il est tout simplement faux.
Utiliser un curseur est une erreur, et un curseur dans un trigger une stupidité.

@++

[StringBuilder]

J'aime bien ces affirmations qui sortent d'on ne sait où...

Que d'un point de vue performances, simplicité de relecture, etc. qu'on préconise fortement certaines méthodologies est une chose.

Mais de là à dire que les autres solutions sont fausses, il y a une certaine marge.

Déjà, si c'était clairement interdit de faire des curseur dans un trigger, pourquoi SQL Server le permettrait ?
=> Il est interdit dans SQL Server de modifier l'état de la base dans une fonction, et SQL Server refuse de compiler une fonction qui tente de modifier des données, ce qui est spécifique à SQL Server. Pourquoi ne pas interdire aux trigger de faire des curseurs alors ?

Ensuite, il n'est pas rare que les traitements nécessaires à la validation d'un enregistrement ne se limitent pas à une vérification ensembliste. A ce moment, cela voudrait dire qu'il ne faut pas le faire par trigger mais par procédure stockée, et ainsi prendre le risque de ne pas utiliser la PS et retrouver ses données dans un état foireux ?

[StringBuilder]

Le trigger d'après ce que tu as indiqué, devrait être :

Code :

1
2
3
4
5
6
7
8
 
CREATE TRIGGER declencheur_suppression
ON DRPI_ProjectData
INSTEAD OF DELETE
AS 
BEGIN
    DELETE DRPI_ProjectData WHERE codeProject IN (SELECT codeProject FROM deleted);
END

[iberserk]

Entièrement d'accord avec elsuket... un curseur doit être évité comme la peste dans un SGBDR tant qu'il peut être remplacé par une opération ensembliste.

Leur présence est souvent le fruit d'une incompétence de la part du développeur vis à vis de la syntaxe SQL.

[elsuket]

Envoyé par StringBuilder

J'aime bien ces affirmations qui sortent d'on ne sait où...

Hé bien cela prouve d'une part que vous ne savez pas ce pour quoi SQL est conçu, et d'autre part que vous ne lisez pas beaucoup ...
Bon nombre d'ouvrages décrivent pourquoi il ne faut pas les utiliser, preuves à l'appui, et aussi comment les éviter (Joe Celko et SQLPro en font partie)

En outre, j'ai écrit un petit billet là-dessus et le moins qu'on puisse dire est que le résultat est édifiant
Mais après tout, mon test est peut-être terriblement faux et je ne lis que des conspirateurs

Envoyé par StringBuilder

Mais de là à dire que les autres solutions sont fausses, il y a une certaine marge.

C'est faux au sens SQL, mais ça fera le boulot ...

Envoyé par StringBuilder

Le trigger d'après ce que tu as indiqué, devrait être

Au IN près, où j'aurais mis une équi-jointure.

Mesure le temps d'exécution pour 100 lignes, et pas sur une table de 3 lignes : y'a pas photo ...

Envoyé par StringBuilder

Déjà, si c'était clairement interdit de faire des curseur dans un trigger, pourquoi SQL Server le permettrait ?
=> Il est interdit dans SQL Server de modifier l'état de la base dans une fonction, et SQL Server refuse de compiler une fonction qui tente de modifier des données, ce qui est spécifique à SQL Server. Pourquoi ne pas interdire aux trigger de faire des curseurs alors ?

La vraie question, c'est pourquoi les curseurs existent ... je ne sais pas du tout, mais je ne doute pas que cela vienne de COBOL tellement la syntaxe en est proche.
Rassurez-vous je n'ai fait de COBOL qu'à l'IUT, j'y étais obligé ... et j'ai vraiment pas aimé

En ce qui concerne les fonctions, je trouve normal qu'elles ne puissent pas modifier les données, puisque ce sont des fonctions.
Elle ne peuvent modifier que les données qui leur sont locales.
D'autre part les performances des fonctions étant médiocres (je ne sais pas ce qu'il en est dans les autres SGBDR), je ne les utilise que pour des contraintes de domaine ou de valeur par défaut, ou pour des colonnes calculées persistantes et/ou indexées.

Ensuite, il n'est pas rare que les traitements nécessaires à la validation d'un enregistrement ne se limitent pas à une vérification ensembliste

Cela parce que la conception de la base de données a été mal faite, ou pire, pas faite du tout.
C'est ce que je fais tous les jours chez mon employeur : normaliser; et quand mon manager voit les gains, il ne me montre pas la porte

A ce moment, cela voudrait dire qu'il ne faut pas le faire par trigger mais par procédure stockée, et ainsi prendre le risque de ne pas utiliser la PS et retrouver ses données dans un état foireux ?

Plus ou moins même réponse que ci-dessus.
Les triggers sont conçus pour implémenter des règles métier complexes, et la règle décrite par lamiae.ch ne l'est clairement pas.

@++

[StringBuilder]

Qu'on ne se méprenne pas sur ma position : je suis parfaitement d'accord avec la formulation d'Iberserk, mais pas avec celle Elsuket.

Il faut éviter autant que possible de faire des traites non ensemblistes (et donc des curseurs) dans un trigger.
Mais je ne suis pas d'accord sur le fait qu'on puisse dire qu'il s'agit d'une erreur : il existe des cas où on ne peut pas faire autrement.

[elsuket]

Je n'ai aucun problème avec la formulation d'iberserk, elle me convient tout à fait.

Mais je ne suis pas d'accord sur le fait qu'on puisse dire qu'il s'agit d'une erreur : il existe des cas où on ne peut pas faire autrement.

Encore une fois : c'est que la conception a été mal faite.
Ne me répondez pas : mais alors comment faites vous pour faire des requêtes de maintenance de bases de données sur plusieurs bases de données ?
J'utilise un WHILE, qui est de toute façon plus rapide qu'un curseur.

@++

[StringBuilder]

Envoyé par elsuket

Les triggers sont conçus pour implémenter des règles métier complexes, et la règle décrite par lamiae.ch ne l'est clairement pas.

1/ Est-ce que j'ai donné une solution à base de curseur ?
NON

2/ Est-ce que j'ai conseillé l'utilisation d'un curseur ?
NON

2/ Est-ce que lamiae.ch a formulé clairement un besoin ?
NON

Trois bonnes raisons pour :

1/ Arrêter de m'attaquer de la sorte, je ne vous ai rien dit, je ne vous ai pas contre-dit, je respecte ce que vous dites, alors faites de même.

2/ Ne pas faire des suppositions sur la lune sans avoir tous les éléments.

[elsuket]

1/ Est-ce que j'ai donné une solution à base de curseur ?
NON

Non, mais vous l'avez conseillé :

Envoyé par StringBuilder

Tu peux boucler dessus (avec un curseur)

2/ Est-ce que j'ai conseillé l'utilisation d'un curseur ?
NON

Vous avez la mémoire courte, voyez ma réponse ci-dessus.

2/ Est-ce que lamiae.ch a formulé clairement un besoin ?
NON

Oui :

Je cherche à developper un trigger de suppresssion pour supprimer un enregistrement d'une vue qui porte sur plusieurs tables .

On demande un trigger, pas nécessairement un curseur ...

@++

[StringBuilder]

Envoyé par elsuket

Encore une fois : c'est que la conception a été mal faite.

Comment pouvez-vous affirmer ça sans avoir :
1/ La moindre idée de la modélisation.
2/ La moindre idée des règles.

[elsuket]

1/ Arrêter de m'attaquer de la sorte, je ne vous ai rien dit, je ne vous ai pas contre-dit, je respecte ce que vous dites, alors faites de même.

Je ne vous ai pas attaqué, j'ai simplement dit que l'utilisation de curseurs est une hérésie dans une base de données relationnelle SQL, et je le maintiens.

2/ Ne pas faire des suppositions sur la lune sans avoir tous les éléments.

Exemple ?

@++

[elsuket]

Comment pouvez-vous affirmer ça sans avoir :
1/ La moindre idée de la modélisation.
2/ La moindre idée des règles.

Pour avoir travaillé chez différents employeurs dont les métiers sont très différents (dont je suis parti de mon propre chef) et leurs clients, et à chaque fois ça a été pareil : une fois la conception refaite (c'est pas toujours de la tarte), plus du tout de curseur.
Marrant, non ?

@++

[lamiae.ch]

Bonjour

j ai declaré mon besoin , tout simple qui est la syntaxe d'un trigger de suppression , vous m 'avez montrer le code

CREATE TRIGGER declencheur_suppression
ON DRPI_ProjectData
INSTEAD OF DELETE
AS
BEGIN
DELETE DRPI_ProjectData WHERE codeProject IN (SELECT codeProject FROM deleted);
END

Maintenant pour supprimer un enregistrement , juste une requête SQL suffit (execute directement le trigger ) ??

si j'execute par exemple la requête suivante :

Delete from DDRPI_ProjectData where codeprojet = 234235 and version = 3 and label = 'vesion ' ;

le trigger sera automatiquement exécuté ??

[iberserk]

Maintenant pour supprimer un enregistrement , juste une requête SQL suffit (execute directement le trigger ) ??

si j'execute par exemple la requête suivante :

Delete from DDRPI_ProjectData where codeprojet = 234235 and version = 3 and label = 'vesion ' ;

le trigger sera automatiquement exécuté ??

Oui tout à fait, un TRIGGER est un déclencheur... il se déclenche automatiquement en fonction de son type(AFTER/INSTEAD OF DELETE/INSERT/UPDATE).

Vous n'avez donc pas à l'appeler vous même...

[StringBuilder]

Ok, vous voulez un exemple ?

Soit une gestion de stocks.

Un traitement batch qui intègre une liste de mouvements de stocks à partir d’évènements commerciaux saisis offline (sur un site web déporté par exemple), sans contrôle de stock au moment de la saisie.

Selon la nature de la commande, des opérations de vérification de stock sont différentes : si la commande est du type "URGENTE", alors elle ne peut être désservie que par le stock physique (le client ne désire pas attendre plus longtemps que le délais de livraison standard). Si la commande est du type "CONTREMARQUE" alors elle ne doit pas faire l'objet d'un contrôle de stock, et doit faire l'objet d'une commande d'approvisionnement spécifique chez le fournisseur (le fournisseur livre directement le client). Si elle est flaguée "INCASSABLE", il est impossible de livrer partiellement la commande : l'ensemble des produits doivent être livrés en une seule fois.

Un produit dispose de plusieurs compteurs :
- Physique (compteur permettant de connaître le nombre d'unités présentes dans le dépôt)
- Réservation client (compteur permettant de connaître le nombre d'unités réservées par des clients)
- Approvisionnement (compteur permettant de connaître le nombre d'unités en cours d'approvisionnement)

Lors de l'insertion des commandes, on veut flaguer chaque poste de la commande (dans l'ordre de saisie sur le site web, afin de respecter la logique ordre FIFO) en indiquant s'il sera honorée en fonction du stock actuel (statut 1), si elle sera honorée en fonction du stock en appro (statut 2), si elle ne pourra être honorée sans la saisie d'une nouvelle appro (statut 3), s'il ne doit faire qu'une réservation de stock en attendant qu'un autre poste le débloque (statut 4). On doit mettre à jour les différents compteurs de sock au fur et à mesure de l'insertion des postes dans la base, afin de garantir qu'un traitement concurrent bénéficie de données à jours.

En raison de la charge importante de la base à toute heure (utilisée dans plusieurs pays), et la volumétrie importante des commandes web dans chaque lot batch, il est nécessaire de passer par un trigger, afin de garantir l'intégrité de l'information entre le moment où on calcul le statut et le moment où la ligne est réellement insérée.

Voilà.

Je voudrais bien savoir comment sans curseur (ou while, faut pas pousser mémé dans les orties, c'est la même chose puisqu'il s'agit d'un curseur implicite) tu comptes faire ce trigger.