| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL,Trigger,CREATE
A copy of this was sent to "A.JARO" <e9225501_at_student.tuwien.ac.at>
(if that email address didn't require changing)
On Thu, 21 Oct 1999 00:22:11 +0200, you wrote:
>hello :)!
>i want to create a trigger, which "creates" an user with password....
>and don't want to use a library. can someone help me pls. :)
>
Until Oracle8i, release 8.1 -- you cannot 'create' objects from a trigger. See the URL in my signature for a paper on autonomous transactions -- that shows how you can do it in 8.1 however.... It also says:
This is a frequently asked question – "How can I create a database object whenever I insert a row into such and such a table". The database object varies from question to question – sometimes people want to create a database USER when they insert into some table, sometimes they want to create a table or sequence. Regardless – autonomous transactions make this possible.
In the past, one might have used DBMS_JOB to schedule the DDL to execute after the transaction commits. This is still a viable option and in many cases is still the correct option. The nice thing about using DBMS_JOB to schedule the DDL is that is offers a way of making DDL transactional. If the trigger queues a job to be executed and that job creates a user account – upon rollback of the parent transaction, the job to create the user will be rolled back as well. No record in your ‘people’ table and no database account. Using autonomous transactions in the same scenario – you will have created the database account but have no record in the people table. Which method you use will be decided upon based on your requirements.
Here is a small example that shows the creation of a database account anytime a user record is placed into the "APPLICATION_USERS" table. Note that the definer of this trigger must have been granted the "CREATE USER" privilege directly (not via some role).
Using DBMS_JOB is (IMO) the 100% correct way to go. It is transactional (if the transaction that created the row in the user table gets rolled back -- so does the job to create the user). It is fast.
The way to do what you want is to create a 'create user' procedure that your trigger can SCHEDULE. your trigger might then be:
....
declare
l_job number;
begin
dbms_job.submit( l_jobno, 'create_user( ''' || :new.username || ''', ''' ||
:new.password || ''' );' );
end;
that will run the create_user procedure sometime AFTER you commit. See the docs for more info on dbms_job.
>
>CREATE TABLE table_name(
> password VARCHAR2(8),
> username VARCHAR2(8)
>);
>
>
>CREATE OR REPLACE PROCEDURE create_user(usr VARCHAR2, passwort VARCHAR2)
>AS
>
> cursor_name NUMBER;
> ret NUMBER;
>
>BEGIN
> cursor_name := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(cursor_name,'CREATE USER '||usr||' identified by
>'||passwort,DBMS_SQL.NATIVE);
> ret := DBMS_SQL.EXECUTE(cursor_name);
> DBMS_SQL.CLOSE_CURSOR(cursor_name);
>
> cursor_name := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(cursor_name,'Grant create session to
>'||usr,DBMS_SQL.NATIVE);
> ret := DBMS_SQL.EXECUTE(cursor_name);
> DBMS_SQL.CLOSE_CURSOR(cursor_name);
>END;
>/
>
>
>CREATE OR REPLACE TRIGGER t_test
>BEFORE INSERT ON table_name FOR EACH ROW
>
>BEGIN
> create_user(:NEW.username,:NEW.password);
>END t_test;
>/
>
>
>THANX, sorry, my english is not well :(
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Oct 21 1999 - 08:06:31 CDT
 |
 |