THANX :)
IT WORKS :)))))
Thomas Kyte schrieb:
> 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:
>
> =======================================================
> Performing DDL in triggers
>
> 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 - 15:06:36 CDT
