PL/SQL,Trigger,CREATE 
Author Message
 PL/SQL,Trigger,CREATE

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. :)

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 :(



Wed, 18 Jun 1902 08:00:00 GMT
 PL/SQL,Trigger,CREATE


(if that email address didn't require changing)

Quote:

>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.

Quote:

>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


Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation



Wed, 18 Jun 1902 08:00:00 GMT
 PL/SQL,Trigger,CREATE
THANX :)
IT WORKS :)))))

Thomas Kyte schrieb:

Quote:

> (if that email address didn't require changing)

> >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


> Oracle Service Industries     Reston, VA   USA

> Opinions are mine and do not necessarily reflect those of Oracle Corporation



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. PL/SQL Q: can't create triggers

2. PL/SQL package to create tables and/or triggers

3. OH-Fairlawn-101453--PowerBuilder-PL/SQL-Stored Procedures-Triggers-ORACLE-SQL*Pl

4. OH-Fairlawn-101453--PowerBuilder-PL/SQL-Stored Procedures-Triggers-ORACLE-SQL*Pl

5. OH-Fairlawn-101453--PowerBuilder-PL/SQL-Stored Procedures-Triggers-ORACLE-SQL*Pl

6. what is the different of application trigger and database trigger in PL/SQL

7. Creating trigger functions in pl/pgsql

8. VA-Northern VA-89513--ORACLE-PL/SQL-SQL *Loader-Triggers-Stored Procedures-Oracle Developer:

9. VA-Northern VA-89513--ORACLE-PL/SQL-SQL *Loader-Triggers-Stored Procedures-Oracle Developer:

10. VA-Northern VA-89513--ORACLE-PL/SQL-SQL *Loader-Triggers-Stored Procedures-Oracle Developer:

11. VA-Northern VA-89513--ORACLE-PL/SQL-SQL *Loader-Triggers-Stored Procedures-Oracle Developer:

12. Using SQL to change trigger PL/SQL text ?


 
Powered by phpBB® Forum Software