Discussion :

[xeron33]

Bonjour à tous et à toutes,
Voilà j'aimerais savoir comment en PL/SQL on peut déprogrammer une contrainte sur une table et la reprogrammer ensuite, dans le cas ou cette contrainte empêche de supprimer un enregistrement par exemple: c'est mon cas en tout cas.
Merçi
A +

[Juda_]

Bonjour,

Une contrainte Oracle est vérifiée selon son status visible avec la requête :

Code :

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

select status from user_constraints where constraint_name = 'ma_condition';

Pour activer ou désactiver une contrainte :
activer :

Code :

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

1
2
3
4
alter table
   ma_table
ENABLE constraint
   ma_contrainte;

ou

désactiver :

Code :

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

1
2
3
4
alter table
   ma_table
DISABLE constraint
   ma_contrainte;

Bonne journée.

[xeron33]

Merci pour votre réponse, mais j'ai essayé de rajouter vos lignes dans mon trigger et ça ne marche pas voiçi le trigger:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
CREATE OR REPLACE TRIGGER  "Suppr Emission" 
after delete on Emission
for each row
declare

wcodpers char(100);
begin
ALTER TABLE presente disable constraint FK_CODEMIS;
select personne.codpers into wcodpers from presente,personne where presente.codemis=:old.codemis and presente.codpers=personne.codpers and personne.nch=:old.nch;
insert into Hist_Present values(:old.codemis,wcodpers,:old.nch,:old.dat_deb_dif,sysdate);
ALTER TABLE presente enable constraint FK_CODEMIS;    
end;

Si vous avez une idée merçi
A +

[Juda_]

Les commandes ALTER sauf exception doivent être fait hors de procédures ou de triggers. Le fait de vouloir activer/désactiver une contrainte dynamiquement signifie je pense que les conditions de la contrainte ou d'exécution du trigger n'est pas bonne.

ça ne marche pas

Qu'entendez vous par là? Il y a une erreur Oracle? Un problème de comportement?

De mon côté je ne vois pas de raison pour que ça ne marche pas.

[Juda_]

Je n'ai pas fait attention en lisant votre code. Mais le fait de désactiver une contrainte pour insérer un enregistrement ne répondant pas à la contrainte, puis réactiver la contrainte en question par la suite : La contrainte ne peut pas être réactivée normalement puisqu'elle ne peut pas se vérifier pour l'enregistrement en question.
Pour activer une contrainte, il faut qu'elle puisse être validée pour les enregistrements existants sinon Oracle renvoie une erreur -02293 qui empêche de réactiver la contrainte.

Bonne journée.
Cordialement,

[xeron33]

Merçi de vous êtes "penché" sur mon problème mais je pense que le problème viens de l'insertion dans mon trigger des commandes ALTER car à la compil j'ai ce message :

5 1 PLS-00103: Symbole "ALTER" rencontré à la place d'un des symboles suivants : begin case declare exit for goto if loop mod null pragma raise return select update while with <

Je vous dois de m'expliquer un peu plus en vous présentant la base sur laquelle je travaille :

Les tables en questions :
Emission(CodEmis,NomEmis,.....);
Histo_Present(CodEmis,CodPers,Nch,Date_Deb,Date_Fin);içi CodEmis est à la fois clé primaire et clé étrangère de la table Emission (2 contraintes donc)
Présente(CodEmis,CodPers); içi CodEmis est clé étrangère de la table Emission et CodPers d'une autre table Personne

Je dois écrire un trigger qui à la suppression d'une emission de la table Emission m'insère automatiquement un enregistrement dans la table Histo_Present, le problème est que je dois renseigner le champ codPers que je "récupère" dans la table Presente avec cette reqûete :

Code :

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

1
2
select personne.codpers into wcodpers from presente,personne where presente.codemis=:old.codemis and presente.codpers=personne.codpers and personne.nch=:old.nch;

et comme il existe une contrainte da

[xeron33]

OUPSS uen erreur de ma part je finis mon post ... pardon !!!

Merçi de vous êtes "penché" sur mon problème mais je pense que le problème viens de l'insertion dans mon trigger des commandes ALTER TABLE presente disable constraint FK_CODEMIS; car à la compil j'ai ce message :
5 1 PLS-00103: Symbole "ALTER" rencontré à la place d'un des symboles suivants : begin case declare exit for goto if loop mod null pragma raise return select update while with <
Je vous dois de m'expliquer un peu plus en vous présentant la base sur laquelle je travaille :
Les tables en questions :
Emission(CodEmis,NomEmis,.....);
Histo_Present(CodEmis,CodPers,Nch,Date_Deb,Date_Fin);içi CodEmis est à la fois clé primaire et clé étrangère de la table Emission (2 contraintes donc)
Présente(CodEmis,CodPers); içi CodEmis est clé étrangère de la table Emission et CodPers d'une autre table Personne

Je dois écrire un trigger qui à la suppression d'une emission de la table Emission m'insère automatiquement un enregistrement dans la table Histo_Present, le problème est que je dois renseigner le champ codPers que je "récupère" dans la table Presente avec cette reqûete :

Code :

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

1
2
select personne.codpers into wcodpers from presente,personne where presente.codemis=:old.codemis and presente.codpers=personne.codpers and personne.nch=:old.nch;

et comme il existe une contrainte dans la table Presente sur le champ CodEMis clé étrangère vers la table Emission le trigger ne fonctionne que si je supprime cette contrainte . C'est pour cela que je pose la question comment déprogrammer une contrainte dans un trigger ? Apparement vu votre précédent réponse ce n'est pas possible dans un trigger comment alors?
Voilà j'epère avoir été clair.
Merçi encore
A +

[skuatamad]

Pour information seulement pour executer du DDL dans du PL/SQL il faut utiliser du SQL dynamique.
Mais ça ne fonctionnera pas dans un trigger car le DDL fait un commit implicite or il est interdit de commit dans un trigger.

Tu dois donc oublier la désactivation de contraintes.

A la place je dirais d'après les infomations fournies qu'il suffit de :
- utiliser ton trigger sans désactivation de contraintes et plutôt en before delete je dirais.
- modifier la contrainte clé étrangère pour y rajouter la clause ON DELETE CASCADE

Donc les lignes seront historisées dans la table Hist_Present puis automatiquement supprimées de la table Presente, grâce à la clause CASCADE.

PS : il faut nommer les colonnes à insérer dans un insert :

Code :

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

insert into (liste_colonnes) values (...)

[Juda_]

Effectivement, comme dit Skuatamad, pour exécuter des commandes DDL il faut utiliser le SQL dynamique, sans voir l'erreur je n'ai pas fait attention de le relever, désolé je suis mal réveillé en ce moment .

Je n'avais pas en fait tester le cas dans un trigger. Mais ça paraît effectivement impossible. D'ailleurs c'est préciser dans la doc Oracle :

SQL Statements Allowed in Trigger Bodies

A trigger body can contain SELECT INTO statements, SELECT statements in cursor definitions, and all other DML statements.

A system trigger body can contain the DDL statements CREATETABLE, ALTERTABLE, DROP TABLE and ALTER COMPILE. A nonsystem trigger body cannot contain DDL or transaction control statements.

Note:
A subprogram invoked by a trigger cannot run the previous transaction control statements, because the subprogram runs within the context of the trigger body.

Statements inside a trigger can reference remote schema objects. However, pay special attention when invoking remote subprograms from within a local trigger. If a timestamp or signature mismatch is found during execution of the trigger, then the remote subprogram is not run, and the trigger is invalidated.

http://docs.oracle.com/cd/B28359_01/...0/triggers.htm

Bonne journée.

[xeron33]

Pour information seulement pour executer du DDL dans du PL/SQL il faut utiliser du SQL dynamique.
Mais ça ne fonctionnera pas dans un trigger car le DDL fait un commit implicite or il est interdit de commit dans un trigger.

Tu dois donc oublier la désactivation de contraintes.

A la place je dirais d'après les infomations fournies qu'il suffit de :
- utiliser ton trigger sans désactivation de contraintes et plutôt en before delete je dirais.
- modifier la contrainte clé étrangère pour y rajouter la clause ON DELETE CASCADE

Donc les lignes seront historisées dans la table Hist_Present puis automatiquement supprimées de la table Presente, grâce à la clause CASCADE.

PS : il faut nommer les colonnes à insérer dans un insert :

Code :

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

insert into (liste_colonnes) values (...)

Merçi pour ton aide,
J'ai modifié dans la table Hist_Present la clé étrangère :

Code :

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

1
2
3
4
 
 CONSTRAINT "FK_CODEMIS_HP" FOREIGN KEY ("CODEMIS")
	  REFERENCES  "EMISSION" ("CODEMIS") ON DELETE CASCADE ENABLE
   )

J'ai aussi désactiver le trigger de suppression de la table EMission mais il ne m'insère aucun enregistrement dans la table Hist_Present quand j'en supprime un de la table Emisssion.
Je pense que vu que la table Hist_Present ne contient pas exactement tous les mêmes champs de la table Emission ça ne peut pas marcher...
Qu'en pense tu ?
A+

[Scriuiw]

Je pense que vu que la table Hist_Present ne contient pas exactement tous les mêmes champs de la table Emission ça ne peut pas marcher...

Je ne vois pas le rapport !
Si ça ne fonctionne pas c'est que tu as dû mal construire ton trigger

[xeron33]

Je ne vois pas le rapport !
Si ça ne fonctionne pas c'est que tu as dû mal construire ton trigger

Merçi de ta réponse, ben je vois pas trop ce qui se passe en tout cas sur un cas simple :
2 tables:
Presente(CodEmis, CodPers); ou CodEMis Clé étrangère vers table Emission :

Emission(CodEmis,NomEMis,.....);CodEMis clé primaire

J'ai modifié ma clé étrangère sur la table présente :

Code :

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

1
2
3
 
CONSTRAINT "FK_CODEMIS_Presente" FOREIGN KEY ("CODEMIS")
	  REFERENCES  "EMISSION" ("CODEMIS") ON DELETE CASCADE ENABLE

Et quand je supprime un enregistrement dans la table Emission j'ai le message suivant :
error ORA-04091: la table SYSTEM.PRESENTE est en mutation ; le déclencheur ou la fonction ne peut la voir ORA-06512: à "SYSTEM.Suppr Emission", ligne 5 ORA-04088: erreur lors d'exécution du déclencheur 'SYSTEM.Suppr Emission'
Quand je modifie cette contrainte :

Code :

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

1
2
3
 
CONSTRAINT "FK_CODEMIS_PRESENTE" FOREIGN KEY ("CODEMIS")
	  REFERENCES  "EMISSION" ("CODEMIS") ENABLE

là je n'ai plus ce message.
Donc ça ne viens pas du trigger.
Je n'arrive pas à utiliser "On Delete Cascade" comment faire ?
Merçi

[Scriuiw]

Création des tables et contraintes

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
DROP TABLE Emission;
CREATE TABLE Emission (
	codemis number(8), 
	nomemis varchar2(30));
ALTER TABLE Emission ADD (
	CONSTRAINT PK_EMISSION PRIMARY KEY (CodEmis));
 
DROP TABLE Presente;
CREATE TABLE Presente (
	codemis number(8), 
	codpers number(8));
ALTER TABLE Presente ADD (
	CONSTRAINT PK_PRESENTE PRIMARY KEY (codemis, codpers));
ALTER TABLE Presente ADD (
	CONSTRAINT FK_CODEMIS FOREIGN KEY (codemis) REFERENCES emission(codemis)
	ON DELETE CASCADE ENABLE);

Jeux d'essai

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
INSERT INTO Emission
(SELECT	1, 'Emission n°1' FROM	dual
UNION ALL
SELECT	2, 'Emission n°2' FROM	dual
UNION ALL
SELECT	3, 'Emission n°3' FROM	dual
UNION ALL
SELECT	4, 'Emission n°4' FROM	dual);
 
 
INSERT INTO Presente
(SELECT	1, 1 FROM	dual
UNION ALL
SELECT	2, 1 FROM	dual
UNION ALL
SELECT	3, 2 FROM	dual
UNION ALL
SELECT	4, 3 FROM	dual);

J'ai donc

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
SQL> select * from emission;
 
   CODEMIS NOMEMIS
---------- ------------------------------
         1 Emission n°1
         2 Emission n°2
         3 Emission n°3
         4 Emission n°4
 
SQL> select * from presente;
 
   CODEMIS    CODPERS
---------- ----------
         1          1
         2          1
         3          2
         4          3

Et je supprime l'émission n°1

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SQL> delete emission where codemis=1;
 
1 ligne supprimée.
 
SQL> select * from presente;
 
   CODEMIS    CODPERS
---------- ----------
         2          1
         3          2
         4          3

Tu n'aurais pas un trigger qui traine sur l'une de tes tables par hasard ?

[xeron33]

Tu n'aurais pas un trigger qui traine sur l'une de tes tables par hasard ?

MErçi infiniment pour ta démonstration, en effet c'est bien un trigger qui empêche de fonctionner le "On Delete Cascade" mais ce trigger j'en ai besoin pour inserer les enregistrements suppimés de la table Emission dans la table Histo_Present.
Je m'explique :
3 tables :
EMission(CodEmis,NomEmis);
Histo_Presente(CodEmis,CodPers,NCh,Date_Deb,Date_Fin);ou CodEmis clé étrangère vers table Emission
Presente(CodEmis,CodPers);ou CodEmis clé étrangère vers table Emission içi "on delete cascade"
A ce stade tout va bien quand j'enlève un enregistrement de la table Emission il est bien enlevé de la table Présente, mais je dois aussi insérer un enregistrement dans la table Histo_Présente et là j'ai écris ce trigger dans la table EMission :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
CREATE OR REPLACE TRIGGER  "Suppr Emission" 
after delete on Emission
for each row
declare
 
wcodpers char(100);
begin
select personne.codpers into wcodpers from presente,personne where presente.codemis=:old.codemis and presente.codpers=personne.codpers and personne.nch=:old.nch;
insert into Hist_Present values(:old.codemis,wcodpers,:old.nch,:old.dat_deb_dif,sysdate);
end;
/
ALTER TRIGGER  "Suppr Emission" ENABLE
/

Trigger qui me permet de récupérer dans la table personne en jointure avec la table presente,le codpers.Quand je supprime un enregistrement dans la table Emission avec ce trigger j'ai ce message

"error ORA-04091: la table SYSTEM.PRESENTE est en mutation ; le déclencheur ou la fonction ne peut la voir ORA-06512: à "SYSTEM.Suppr Emission", ligne 5 ORA-04088: erreur lors d'exécution du déclencheur 'SYSTEM.Suppr Emission'"

Si tu as une idée...
Merçi encore et à +

[Scriuiw]

N'ayant pas le modèle devant les yeux ça me saute aux yeux...
... A quoi sert ton SELECT sur les tables PERSONNE ET PRESENTE si c'est pour récupérer le CODPERS ?

La sélection avec NCH est nécessaire à ton besoin ?

Code :

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

1
2
3
4
5
6
7
8
SELECT personne.codpers 
INTO     wcodpers 
FROM    presente,personne 
WHERE presente.codemis=:old.codemis 
AND      presente.codpers=personne.codpers 
AND      personne.nch=:old.nch;

INSERT INTO Hist_Present VALUES(:old.codemis,wcodpers,:old.nch,:old.dat_deb_dif,sysdate);

N'est-il pas possible de faire l'INSERT directement ?

Code :

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

INSERT INTO Hist_Present VALUES(:old.codemis,:old.codpers,:old.nch,:old.dat_deb_dif,sysdate);

EDIT :
Je me suis un peu perdu en fait mais je pense qu'il y a quelque chose qui n'est pas clair car ton modèle est ainsi :

EMission(CodEmis,NomEmis);
Histo_Presente(CodEmis,CodPers,NCh,Date_Deb,Date_Fin);ou CodEmis clé étrangère vers table Emission
Presente(CodEmis,CodPers);ou CodEmis clé étrangère vers table Emission içi "on delete cascade"

S'il existe une FK entre ta table Historique et ta table Emission, il va être clairement impossible d'archiver les émissions supprimées !

De plus, dans mon esprit il me semblait que la table HIST_PRESENT est une "image" des enregistrements supprimés de la table PRESENTE.
Si c'est le cas, le trigger doit alors être rattaché à la table PRESENTE et non à la table EMISSION.

Peux-tu clarifier ces points ?
Merci.

[xeron33]

Merci encore de t’intéresser de si prêt à mon cas c'est très sympa et très intéressant !

A quoi sert ton SELECT sur les tables PERSONNE ET PRESENTE si c'est pour récupérer le CODPERS ?[/B]
CodPers ne se trouve pas dans la table Emission, c'est pour ça cette requete avec jointure tables presente,personne ou se trouve CodPers:
Personne(CodPers,Nom,Prenom.....,NCH); où COdPers est clé primaire

N'est-il pas possible de faire l'INSERT directement ?
du coup non

S'il existe une FK entre ta table Historique et ta table Emission, il va être clairement impossible d'archiver les émissions supprimées !
Tout à fait d'accord je l'ai supprimée j'ai créé un contrainte FK à la place sur le champ CodPers :

Code :

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

1
2
3
 
CONSTRAINT "FK_CODPERS_HP" FOREIGN KEY ("CODPERS")
	  REFERENCES  "PERSONNE" ("CODPERS") ENABLE

mais je me demande si cette contrainte me sera utile je verrai plus tard...

De plus, dans mon esprit il me semblait que la table HIST_PRESENT est une "image" des enregistrements supprimés de la table PRESENTE.
Si c'est le cas, le trigger doit alors être rattaché à la table PRESENTE et non à la table EMISSION.
Oui en effet c'est bien une image mais je vois pas trop pourquoi ça te gène, car le trigger insère après suppression de la table Emission dans la table Histo_Present.
Pour ma part j'ai modifié mon plan :
avant :
EMission(CodEmis,NomEmis);
Histo_Presente(CodEmis,CodPers,NCh,Date_Deb,Date_Fin);ou CodEmis clé étrangère vers table Emission
Presente(CodEmis,CodPers);ou CodEmis clé étrangère vers table Emission içi "on delete cascade"

maintenant :

EMission(CodEmis,NomEmis); inchangé
Histo_Presente(CodEmis,CodPers,NCh,Date_Deb,Date_Fin);CodPers clé étrangère vers table Personne
Presente(CodEmis,CodPers);ou CodEmis clé étrangère vers table Emission plus de "on delete cascade"

Et le trigger sur la table EMission qui supprime les enregistrements sur la table Presente:

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
CREATE OR REPLACE TRIGGER  "Suppr Emission" 
before delete on Emission
for each row
declare

wcodpers char(100);
begin
select personne.codpers into wcodpers from presente,personne where presente.codemis=:old.codemis and presente.codpers=personne.codpers and personne.nch=:old.nch;
insert into Hist_Present values(:old.codemis,wcodpers,:old.nch,:old.dat_deb_dif,sysdate);
delete from presente where presente.codemis=:old.codemis;
end;
/
ALTER TRIGGER  "Suppr Emission" ENABLE
/

Et là maintenant ça fonctionne.
Merçi de ton aide dis moi ce que tu en penses c'est toujours enrichissant.
A +

[Scriuiw]

J'aurai juste une dernière interrogation à propos de ton trigger

Actuellement tu as :
1- Suppression d'un enregistrement dans EMISSION
2- Insertion des enregistrements de la table PRESENTE dans HIST_PRESENTE pour l'émission en cours de suppression
3- Suppression dans la table PRESENTE des enregistrements de l'émission en cours de suppression

Par contre vu que ton trigger est positionné sur la table EMISSION, est-ce normal si tu effectues une suppression d'enregistrements dans la table PRESENTE de ne pas les "basculer" dans ta table d'archivage HIST_PRESENT ?

[xeron33]

J'aurai juste une dernière interrogation à propos de ton trigger

Actuellement tu as :
1- Suppression d'un enregistrement dans EMISSION
2- Insertion des enregistrements de la table PRESENTE dans HIST_PRESENTE pour l'émission en cours de suppression
3- Suppression dans la table PRESENTE des enregistrements de l'émission en cours de suppression

Par contre vu que ton trigger est positionné sur la table EMISSION, est-ce normal si tu effectues une suppression d'enregistrements dans la table PRESENTE de ne pas les "basculer" dans ta table d'archivage HIST_PRESENT ?

Oui effectivement avec mon système si l'émission est présentée par 2 présentateurs sur différentes chaine on a pas l'historique de toutes les présentations mais seulement le CodPers correspondant au n° de chaine saisi dans EMission, mais :
1) si je désactive mon trigger de suppression de la table Emission quand je vais vouloir supprimer un enregistrement de la table Emission la contrainte FK de la table Presente va m'enpécher de le faire
2)Comment à partir de la table Presente créer un trigger gérant les suppressions de la table EMission ?
Si tu as une idée pour cela merçi de me le faire savoir
A +