Trigger de suppression SQL Server

Citation:

---

Envoyé par 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é ??

---

Y'a un truc qui me turlupine :

Le trigger porte sur votre vue.
Et il doit effacer des données dans la table.

Là, j'ai l'impression qu'à la faute de frappe près, votre trigger efface des données dans l'objet sur lequel il est.
Bon, dans certains cas, c'est possible, mais je doute que ce soit réellement ce que vous désirez faire.

Citation:

---

Envoyé par 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.

---

Tout votre traitement peut être fait en un jeu de n requêtes SQL et l'ordre de prise en compte des lignes piloté par des fonctions de fenêtrage comme ROW_NUMBER().

SQL est un langage complet au sens de Turing, dès lors n'importe quel traitement peut être effectué par une ou plusieurs requêtes sans jamais sombrer dans l'itératif (code procédural).
La condition sine qua non est que le problème doit être bien posé, c'est à dire que les tous les éléments nécessaire a son traitement aient été fournis (donc une modélisation des données adaptée au traitement).

On voit par exemple souvent des développeurs vous dire qu'il est impossible de faire tel traitement avec une seule requête... par exemple lorsque l'on doit appeler une procédure stockée sur un jeu de lignes... !
mais il suffit tout simplement d'inverser le principe et d'intégrer la requête générant des lignes au code de la procédure pour s'apercevoir que l'itératif n'est dès lors plus nécessaire !

A +

Je veux bien croire que ce soit possible (même si j'ai quelques réserves, pour cela il faudrait partir d'un véritable cas concret), mais je maintiens quand même qu'il y a une nuance entre :
- Eviter autant que possible de faire des curseurs dans des triggers (et tout autre élément procédural)
- Considérer comme une erreur grave de conception/analyse l'utilisation d'un curseur dans un trigger

Ceci pour plusieurs raisons :
- Je maintien que je suis persuadé qu'aussi complet soit le SQL, il existe toujours des cas qui ne rentrent pas dans les clous, sans que la modélisation ou l'analyse ne soit forcément responsables (les besoins à la con, chez les clients il y en a à profusion, c'est une source intarissable)
- Que selon les versions du SGBD utilisé, un nombre conséquent de fonctionnalités de SQL ne sont pas présentes, et par conséquent qu'il ,n'est pas forcément possible de faire ces traitements avec de simples requêtes SQL
- Que souvent, c'est ce que j'ai pu constater sur ce forum, les solutions "soit disant toutes parfaites sans itérations" font imposent des compromis en terme d'évolutibilité (nombre d'auto-jointures limitées à un nombre fixe lors d'une conversion en pivot par exemple).

Citation:

---

Envoyé par StringBuilder

Je veux bien croire que ce soit possible (même si j'ai quelques réserves [....]
Ceci pour plusieurs raisons :
- Je maintien que je suis persuadé qu'aussi complet soit le SQL, il existe toujours des cas qui ne rentrent pas dans les clous, sans que la modélisation ou l'analyse ne soit forcément responsables (les besoins à la con, chez les clients il y en a à profusion, c'est une source intarissable)

---

Justement je suis à l'écote d'un cas concret... Après 15 ans d'audit et plus de 20 dans les SGBDR, je n'en ais pas encore vu un seul. En revanche il n'est pas toujours souhaitable de dépenser du temps pour des choses peu importante. Les procédures de maintenance en étant un excellent exemple !

Citation:

---

- Que selon les versions du SGBD utilisé, un nombre conséquent de fonctionnalités de SQL ne sont pas présentes, et par conséquent qu'il ,n'est pas forcément possible de faire ces traitements avec de simples requêtes SQL

---

Si l'on en reste à 2005 et avec l'apport des requêtes récursive, tout est possible (turing complet)

Citation:

---

- Que souvent, c'est ce que j'ai pu constater sur ce forum, les solutions "soit disant toutes parfaites sans itérations" font imposent des compromis en terme d'évolutibilité (nombre d'auto-jointures limitées à un nombre fixe lors d'une conversion en pivot par exemple).

---

PIVOT est l'exemple parfait du non exemple ! Il s'agit de faire de la cosmétique sur un SGBDR qui n'a pas vocation à cela.
Si vous utilisez un marteau pour retirer un clou, ça va forcément marcher pas terrible !

A +

J'ai des probleme lors de l exécution
voila mon trigger

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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     DELETE FROM DRPI_ProjectData WHERE codeProject IN (SELECT codeProject FROM deleted where codeProject =@codeprojet and version = @version and label=@label );   END

---

bien enregistré .

quand j exécuté ma requête

Code:

---

1 2	Delete from DRPI_ProjectData where codeProject = '2B001' AND version = '3' and label='Nom de version'

---

j'ai une erreur d exécution

Citation:

---

Msg 4405, Level 16, State 1, Procedure declencheur_suppression, Line 50
View or function 'DRPI_ProjectData' is not updatable because the modification affects multiple base tables.

---

Y'a un truc qui me turlupine :

Le trigger porte sur votre vue.
Et il doit effacer des données dans la table.

Là, j'ai l'impression qu'à la faute de frappe près, votre trigger efface des données dans l'objet sur lequel il est.
Bon, dans certains cas, c'est possible, mais je doute que ce soit réellement ce que vous désirez faire.
Aujourd'hui 10h05


réellement je desire effacer un enregistremennt d une vue qui porte sur plusieurs tables

C'est bien ce qu'il me semblait : vous confondez plusieurs choses.

Une vue ne contient pas de données.
Il s'agit d'une requête qui porte sur plusieurs tables.
La requête est exécutée à chaque fois que vous interrogez la vue, et le résultat n'est pas stocké. Ce que vous voyez, ce sont les données des différentes tables appelées par la vue.

Donc pour "supprimer" une ligne d'une vue, il y a deux solutions :
- Soit il s'agit de supprimer une ligne dans une ou plusieurs des tables utilisées par la vue
- Soit il s'agit de masquer une ligne dans le résultat de la requête

Un exemple plutôt qu'un long discours :

Table "personne" (id, nom, prenom, profession_id)
Table "profession" (id, nom)
Table contrat" (id, personne_id, montant, date)

Une vue :

Code:

---

1 2 3 4 5 6 7

create view v_contrats as select pr.id pro_id, pr.nom, pe.id per_id, pe.nom, pe.prenom, c.id con_id, c.montant, c.date from contrat c inner join personne pe on pe.id c.personne_id inner join profession pr on pr.id = pe.profession_id;

---

Vous souhaitez supprimer des données de la vue :

Code:

---

delete v_contrats where pe_id = 2;

---

Vous avez le choix entre :
1/ Supprimer uniquement les lignes dans contrat rattachées à la personne 2

Code:

---

1 2 3 4 5 6 7 8 9 10

create trigger trg1 on v_contrats instead of delete as begin delete contrats c from deleted d where c.id = d.con_id; end;

---

2/ Supprimer les contrats de la personne 2 ainsi que la personne 2

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

create trigger trg2 on v_contrats instead of delete as begin delete contrats c from deleted d where c.id = d.con_id;   delete personne p from contrat c left outer join deleted d on d.per_id = c.personne_id where p.id = d.per_id and d.per_id is null; end;

---

3/ Modifier la vue pour faire une jointure sur "contrat_exclu", et inserrer dans cette table les contrat_id de la personne 2 afin de les exclure du résultat

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

create table contrat_exclu (id int primary key references contrat(id));   alter view v_contrats as select pr.id pro_id, pr.nom, pe.id per_id, pe.nom, pe.prenom, c.id con_id, c.montant, c.date from contrat c inner join personne pe on pe.id c.personne_id inner join profession pr on pr.id = pe.profession_id left outer join contrat_exclu e on e.id = c.id where e.id is null;   create trigger trg3 on v_contrats instead of delete as begin insert into contrat_exclu select con_id from deleted; end;

---

Ces trois solutions permettent de "supprimer" des lignes dans la vue "v_contrat".

Cependant, elle le font de façon différente.
A toi de trouver celle qui te convient.

Merci pour votre explication , maintenant la suppression est plus claire pour moi :)

Vous pouvez aussi directement supprimer dans les tables à travers la vue sans passer par un trigger INSTEAD OF.
Il suffit que les DELETEs ne porte jamais que sur une table et identifie les lignes visées de manière certaine.

Pour un exemple voir les exemples de la règle de Codd n°6 dans la papier que j'ai écrit ici : http://sqlpro.developpez.com/SGBDR/ReglesCodd/

A +

Salut

:oops: je suis de retour :
voila mon trigger de suppression :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

ALTER TRIGGER [dbo].[declencheur_suppression]   ON [dbo].[DRPI_ProjectData] instead of DELETE AS   Declare @Projectid int , @version int   BEGIN   delete from DRPI_File where DRPI_File .FileID IN (Select d.FileID from deleted d where d.version = @version and d.ProjectID = @Projectid and d.label = 'Nom de version ')     END

---

quand je lance ma requête de suppression :

Code:

---

Delete from DRPI_ProjectData where ProjectID = '138'and version = '3'

---

et j 'ai comme message d 'exuction

Code:

---

1 2 3

(0 row(s) affected)   (48 row(s) affected)

---

je trouve qu'aucune suppression ne s'effectue , Pouviez vous m expliquer pourquoi ??

Merci d avance