Trigger at the Schema level

From: Jawahar Rajan <jrajan_at_cts-du.com>
Date: Tue, 23 Dec 2003 08:50:16 GMT
Message-ID: <clTFb.189033$I53.7835741@twister.southeast.rr.com>

All,
I am trying to use the following trigger. It is a Schema level trigger, that should run each time a table is created. I am trying to create a shadow table for eachnew table created. However I get an error that say I do not have permissions?

Any ideas why
The error:
ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
ORA-06512: at line 39

Do I need tobe a DBA to get the trigger to fire? Any Suggestions welcome

Jawahar

create or replace trigger Table_Mod_TR
After create on SCHEMA

DECLARE
-- variable to hold dynamic SLQ

v_create varchar2 (300);

A cursor to get the columns for the table CURSOR get_columns (tbl_owner VARCHAR2,tbl_table VARCHAR2) IS SELECT column_name,data_type,data_length FROM all_tab_columns WHERE owner = tbl_owner and table_name = tbl_table; BEGIN

IF (ORA_SYSEVENT = 'CREATE') Then

If (ORA_DICT_OBJ_TYPE = 'TABLE') Then

v_Create := 'CREATE TABLE ';
v_create := v_create || ora_dict_obj_name || '_A (';

For v_col_rec in get_columns (
ora_dict_obj_owner,ora_dict_obj_Name)

loop

start the creation of the Audit Table v_create := v_create || v_col_rec.column_name || ' ' || v_col_rec.data_Type || '(' || v_col_rec.data_length || '),';

End Loop;

v_create := substr(v_create,1,length(v_create)-1) || ')'; End if;
End If;
Execute Immediate v_create;

End Table_Mod_TR; Received on Tue Dec 23 2003 - 02:50:16 CST

Trigger at the Schema level - not working