Trigger de suppression SQL Server

Version imprimable

27/09/2011, 10h26
lamiae.ch

Trigger de suppression SQL Server

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 :)
27/09/2011, 10h36
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 ?
27/09/2011, 11h19
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 !!

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;

27/09/2011, 12h03
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.
27/09/2011, 12h17
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é.

@++ ;)
27/09/2011, 14h34
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 ?

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

27/09/2011, 16h55
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.
27/09/2011, 17h17
elsuket

Citation:

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

Citation:

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

Citation:

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

Citation:

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.

Citation:

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

Citation:

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.

@++ ;)
27/09/2011, 17h19
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.
27/09/2011, 17h23
elsuket

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

Citation:

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.

@++ ;)
27/09/2011, 17h24
StringBuilder

Citation:

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.
27/09/2011, 17h27
elsuket

Citation:

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

Non, mais vous l'avez conseillé :

Citation:

Envoyé par StringBuilder

Tu peux boucler dessus (avec un curseur)

Citation:

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.

Citation:

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

Oui :

Citation:

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

@++ ;)
27/09/2011, 17h28
StringBuilder

Citation:

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.
27/09/2011, 17h29
elsuket

Citation:

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.

Citation:

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

Exemple ?

@++ ;)
27/09/2011, 17h30
elsuket

Citation:

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 ?

@++ ;)
28/09/2011, 09h51
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é ??
28/09/2011, 09h53
iberserk

Citation:

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...
28/09/2011, 10h05
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.
28/09/2011, 10h09
StringBuilder

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.
28/09/2011, 10h54
SQLpro

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 +
28/09/2011, 11h40
StringBuilder

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).
28/09/2011, 11h58
SQLpro

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 +

Erreur

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.

28/09/2011, 12h03
lamiae.ch

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.

28/09/2011, 13h50
lamiae.ch

Merci pour votre explication , maintenant la suppression est plus claire pour moi :)
28/09/2011, 13h55
SQLpro

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