Déprogrammer une contrainte

Version imprimable

29/02/2012, 23h20
xeron33

Déprogrammer une contrainte

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 +
01/03/2012, 08h56
Juda_
Bonjour,

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

Code:

select status from user_constraints where constraint_name = 'ma_condition';

Pour activer ou désactiver une contrainte :
activer :
Code:

1 2 3 4 alter table ma_table ENABLE constraint ma_contrainte;
ou

désactiver :
Code:

1 2 3 4 alter table ma_table DISABLE constraint ma_contrainte;
Bonne journée.

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:

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 +

05/03/2012, 09h05
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.

Citation:

ç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.
05/03/2012, 09h33
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,
05/03/2012, 19h11
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 :

Citation:

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:

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
05/03/2012, 19h23
xeron33

Réponse à Juda
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:

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 +
05/03/2012, 21h02
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:

insert into (liste_colonnes) values (...)
06/03/2012, 09h17
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 :

Citation:

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.
09/03/2012, 23h24
xeron33
Citation:

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

insert into (liste_colonnes) values (...)

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

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+
12/03/2012, 09h02
Scriuiw

Citation:

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
12/03/2012, 23h30
xeron33

réponse à Scriuiw
Citation:

Envoyé par Scriuiw

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:

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:

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

Création des tables et contraintes

Code:

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:

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:

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:

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 ?

réponse à Scriuiw

Citation:

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:

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

Citation:

"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 à +

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:

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:

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 :

Citation:

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.

réponse à Scriuiw

Merci encore de t’intéresser de si prêt à mon cas c'est très sympa et très intéressant !
Citation:
Envoyé par Scriuiw;
[B
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:

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:

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 +

15/03/2012, 09h06
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 ?
16/03/2012, 02h18
xeron33

réponse à Scriuiw

Citation:

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

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 +