Re: Automating Trigger creation for a table?

Yes, but if you want to do it this way, you need to use dbms_sql. So your trigger code should go into a string (32k max), and then
cur_handle := dbms_sql.open_cursor;
dbms_sql.parse(cur_handle, yourcommandstr, dbms_sql.native); dbms_sql.close_cursor(cur_handle);

cur_handle is a binary integer.
:new btw is trigger specific.
You are on the right track,
but as I tried to explain your coding is not going to work. Also I believe trigger names are in their own namespace, you should one way or another include the tablename in your triggername.

Hth,

Sybrand Bakker, Oracle DBA

<newopt_at_my-deja.com> wrote in message news:889fob$rvm$1_at_nnrp1.deja.com...
> I created the script below to automate the process of creating a
> universal trigger for a set of tables that I am loading. I keep getting
> a bind variable "NEW" not declared error.
> Any help would be appreciated!
>
> SQL> get trigger.sql
> 1 Declare
> 2 cid INTEGER;
> 3 v_tablename Vchar2(10);
> 4 -- This procedure is used to create the trigger to preload the
> ICOMS
> 5 -- NCR table.
> 6 procedure create_trigger (v_trigger_tablename Varchar2(10)) IS
> 7 BEGIN
> 8 CREATE OR REPLACE
> 9 TRIGGER PRELOAD_TRIGGER
> 10 BEFORE INSERT
> 11 ON v_trigger_tablename
> 12 FOR EACH ROW
> 13 BEGIN
> 14 SELECT seq_number_next.NEXTVAL INTO :new.seq_number FROM DUAL;
> 15 END;
> 16 END create_trigger;
> 17 -- Mainline Processing
> 18 BEGIN
> 19 -- Load the CBI0REP Table
> 20 v_tablename := 'cbi0rep';
> 21 create_trigger(v_tablename);
> 22* END;
> 23 /
> Bind variable "NEW" not declared.
> SQL>
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Mon Feb 14 2000 - 14:31:26 CST