Erreur dans corps du trigger ! [Résolu]

[nouraty]

bonjour,
j'essaie de créer un utilisateur dans un trigger suite à une insertion dans une table.
je récupère une erreur lors de l'insertion dans la table dû au trigger mais je ne vois pas c'est quoi au juste
PS: l'utilisateur qui exécute ceci à le droit de créer des utilisateur!
le voila mon code:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
 
CREATE OR REPLACE TRIGGER a
before INSERT ON COMPTE
FOR each row
declare 
begin
EXECUTE IMMEDIATE 'CREATE USER '|| :new.nom_c||' IDENTIFIED BY '||:new.pwd_c 
				|| 'Default tablespace ts_tables Quota 5M on ts_tables'
				||' Temporary tablespace ts_temp  Quota 10M on ts_temp';
EXECUTE IMMEDIATE 'grant create session to '|| :new.nom_c;
end
;
/

Code :

1
2
 
INSERT INTO compte VALUES(3,'sara','sara','nulpart','sara@live.fr','sara',0,'p');

l'erreure:

Code :

1
2
3
4
 
ERREUR à la ligne 1 :
ORA-04098: Déclencheur  'ADMIN_BIB.CONN_USER' non valide. Echec de la
revalidation

Merci de bien vouloire m'aider

[McM]

Quelle est la règle N°1 quand on code de l'execute immediate ?

Réponse : Toujours AFFICHER la chaine exécutée et vérifier que la requête est correcte.

[nouraty]

le fais dans les autres languages, pas encore de bonnes manières en pl/sql

maintenant je l'ai essayé comme ceci ( j'espére ne pas avoir commis de bétises)

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
SET serveroutput ON;
CREATE OR REPLACE TRIGGER a
before INSERT ON COMPTE
FOR each row
declare 
tst1 varchar2(400);
tst2 varchar2(50);
begin
tst1 :='CREATE USER '|| :new.nom_c||' IDENTIFIED BY '||:new.pwd_c 
				|| 'Default tablespace bib1_tables Quota 5M on bib1_tables'
				|| ' Temporary tablespace bib_temp  Quota 10M on bib_temp';
dbms_output.put_line(tst1);
tst2 :='grant create session to '|| :new.nom_c;
dbms_output.put_line(tst2);
end
;
/

et ça retourne toujours la même erreure!

[mnitu]

Ça ne va pas aller: create user c’est du DDL et le DDL commit, ce qui n’est pas permis pour le trigger sauf s’il s’exécute dans une transaction autonome; mais ça montre souvent une incompréhension des triggers et transactions autonomes.
Utilisez donc DBMS_JOB via le trigger pour créer votre utilisateur !

[Garuda]

Il manque un espace devant 'default tablespace'

[McM]

Envoyé par Garuda

Il manque un espace devant 'default tablespace'

Visiblement l'erreur est au niveau compil du trigger et pas de l'exécution (Déclencheur 'ADMIN_BIB.CONN_USER' non valide)
Faire un show errors après la compil pour vérifier les erreurs.
Chez moi il se compile bien (en mettant des valeurs de colonne des :new correspondant à ma table compte).

[Pomalaix]

Envoyé par mnitu

Utilisez donc DBMS_JOB via le trigger pour créer votre utilisateur !

Pour moi, avoir des opérations non transactionnelles ou asynchrones dans un déclencheur DML est une hérésie.
Il me paraît nettement plus propre de créer une procédure creer_compte, qui se chargera de créer le compte si nécessaire, et de l'insérer dans la table.

[mnitu]

Envoyé par Pomalaix

Pour moi, avoir des opérations non transactionnelles ou asynchrones dans un déclencheur DML est une hérésie.
Il me paraît nettement plus propre de créer une procédure creer_compte, qui se chargera de créer le compte si nécessaire, et de l'insérer dans la table.

Ca reste toujours discutable. Je pense que le plus important est de bien comprendre qu’elles sont les implications. Ensuite le choix de la solution pourrait dépendre des autres contraintes.

[nouraty]

excusez mes lacunes.
Si j'ai bien compris je ne peut pas créer des utilisateurs via des triggers, je dois passer par un DBMS_JOB. mais d'après ce que j'ai lu DBMS_JOB c'est pour exécuter des requêtes chaque t moment alors que moi je veux le faire a chaque insertion dans la table compte.
j'essaierais la proposition de Pomalaix de créer la procedure puis l'appeler dans le trigger

[Pomalaix]

Envoyé par nouraty

Si j'ai bien compris je ne peut pas créer des utilisateurs via des triggers, je dois passer par un DBMS_JOB. mais d'après ce que j'ai lu DBMS_JOB c'est pour exécuter des requêtes chaque t moment alors que moi je veux le faire a chaque insertion dans la table compte.
j'essaierais la proposition de Pomalaix de créer la procedure puis l'appeler dans le trigger

Vous pouvez techniquement tout à fait créer un utilisateur dans un déclencheur, mais comme disait Mnitu, ça nécessite que ce déclencheur soit défini en transaction autonome (car une opération DDL comme cette création d'utilisateur provoque un COMMIT implicite, ce qui est interdit dans un déclencheur ordinaire).

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
connect system/system
GRANT CREATE user TO system;
 
CREATE TABLE compte(nom varchar2(30));
 
CREATE OR REPLACE TRIGGER trig_compte
before INSERT ON compte
FOR each row
declare
	pragma autonomous_transaction;
begin
	execute immediate 'create user ' || :new.nom || ' identified by ' || :new.nom;
end;
/
 
INSERT INTO compte VALUES('DEMO1');
INSERT INTO compte VALUES('DEMO2');

Moi je ne suis pas partisan de cette méthode, et je préfère une procédure que vous appellerez à la place de l'INSERT.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
DROP TRIGGER trig_compte;
 
CREATE OR REPLACE procedure nouveau_compte(p_nom varchar2)
IS
begin
	INSERT INTO compte VALUES(p_nom);
	execute immediate 'create user ' || p_nom || ' identified by ' || p_nom;	
end;
/
 
exec nouveau_compte('DEMO3');
exec nouveau_compte('DEMO4');

[nouraty]

merci beaucoup
avec pragma autonomous_transaction;
toujours des problemes
mais la deuxieme méthode de

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
 
DROP TRIGGER trig_compte;
 
CREATE OR REPLACE procedure nouveau_compte(p_nom varchar2)
IS
begin
	INSERT INTO compte VALUES(p_nom);
	execute immediate 'create user ' || p_nom || ' identified by ' || p_nom;	
end;
/
 
exec nouveau_compte('DEMO3');
exec nouveau_compte('DEMO4');

c bon je crée les utilisateurs
une question : est ce que je peux appeler cette procédure dans mon trigger sans aucun problème, parce que j'avais essayé de créer l'utilisateur dans une procédure et appeler cette dernière dans un trigger, c'était pas bien le résultat

[mnitu]

Envoyé par nouraty

...
une question : est ce que je peux appeler cette procédure dans mon trigger sans aucun problème, parce que j'avais essayé de créer l'utilisateur dans une procédure et appeler cette dernière dans un trigger, c'était pas bien le résultat

Peu importe que vous exécutez le DDL directement dans le trigger ou dans une procédure appelée par le trigger, il y aurait toujours un commit implicite ce qui est interdit dans le trigger. C’est pour ça que vous devez soit utiliser les transactions autonomes, le commit s'effectue dans une autre transaction, soit passer par le package DBMS_JOB pour demander la création asynchrone de l’utilisateur, ce qui se fera dans une autre transaction bien sûr.
Maintenant ce que vous propose Pomalaix est de complètement abandonner le trigger et de regrouper les deux actions dans une procédure que vous allez appeler à la place de l’insert dans votre table compte.

[Pomalaix]

Envoyé par nouraty

avec pragma autonomous_transaction;
toujours des problemes

Oui, et lesquels ? Tant que vous ne précisez pas votre source PL/SQL et le message d'erreur, on ne peut rien faire pour vous.

[nouraty]

Envoyé par Pomalaix

Oui, et lesquels ? Tant que vous ne précisez pas votre source PL/SQL et le message d'erreur, on ne peut rien faire pour vous.

je voulais dire le même problème qu'avant

Code :

1
2
3
ERREUR à la ligne 1 :
ORA-04098: Déclencheur  'ADMIN_BIB.CONN_USER' non valide. Echec de la
revalidation

Maintenant ce que vous propose Pomalaix est de complètement abandonner le trigger et de regrouper les deux actions dans une procédure que vous allez appeler à la place de l’insert dans votre table compte.

oui c'est vrai, et même que ça ne soit pas le point avec lequel j'ai commencé ce n'est pas grave pour mon cas! du moins pas pour le moment.
Je reviens sur les DBLS_JOB, un exemple simple pour leurs utilisation selon ce que j'ai compris:

Code :

1
2
3
4
 dbms_job.submit(
   what=>'ma_requete.sql;',
   next_date=>sysdate+1/24, -- start next hour
   interval=>'sysdate+1/24');  -- Run every hour

je ne vois pas ici deja est ce que ma_requete.sql contiendra l'insertion dans la table, la création du user ou les deux! et puis avec le next_date et intervalle ça va se répéter après chaque intervalle !!

[mnitu]

Dans dbms_job vous n'envoyez que la partie non-transactionnelle : create user …
L’exécution du dbms_job doit être immédiate et il n’y a aucune raison de ré exécuter ce job.

[nouraty]

Envoyé par mnitu

Dans dbms_job vous n'envoyez que la partie non-transactionnelle : create user …
L’exécution du dbms_job doit être immédiate et il n’y a aucune raison de ré exécuter ce job.

dans ce cas a quoi sert le interval=>'sysdate+1/24') et je suppose puisqu'on n'envoie que la partie non transactionnelle je dois l'appeler au moment de l'insertion n'est ce pas? alors comment faire?