Re: how to create a trigger with a variable name
Date: 1998/01/22
Message-ID: <34c6a571.719504_at_192.86.155.100>#1/1
On Wed, 21 Jan 1998 15:02:02 +0100, "JivZ" <jivz_at_NOSPAM.dse.nl> wrote:
>Hi, I want to create a trigger in a stored procedure where the name of the
>trigger comes from a parameter, something like
>
>PROCEDURE ADDTRIGGER(name IN VARCHAR2(50))
>IS
>BEGIN
> CREATE TRIGGER name AFTER ....
>
>END;
>
>How can this be done?
With dynamic sql. For example:
create or replace procedure execute_immediate( stmt in varchar2 )
as
exec_cursor integer default dbms_sql.open_cursor;
rows_processed number default 0;
begin
dbms_output.put_line( '"' || stmt || '"' );
dbms_sql.parse(exec_cursor, stmt, dbms_sql.native );
rows_processed := dbms_sql.execute(exec_cursor);
dbms_sql.close_cursor( exec_cursor );
exception
when others then
if dbms_sql.is_open(exec_cursor) then
dbms_sql.close_cursor(exec_cursor);
end if;
raise;
end;
/
And then
>PROCEDURE ADDTRIGGER(name IN VARCHAR2(50))
>IS
>BEGIN
> execute_immediate( 'CREATE TRIGGER ' || name || ' AFTER ...' );
>
>END;
Just remember, roles are *never* enabled in stored procedures, therefore, in
order to create a trigger in a procedure, the owner of the procedure must have
the create trigger privelege granted directly to them. If you want to test if a
given class of statement will work in a stored procedure with dbms_sql, you
could in sql*plus:
SQL> set role none;
SQL> create trigger .....
If the trigger creates in sqlplus without any roles, you'll be able to create it in the trigger.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Jan 22 1998 - 00:00:00 CET