Another problem to create a system trigger

[mzhu]

Connected with USER1

SQL> create or replace trigger CREATE_TABLE
2 after create on USER1.schema
3 declare
4 sql_stmt varchar2(100);
5 begin
6 if ora_dict_obj_type = 'TABLE' then
7 sql_stmt := 'grant select on ' || ora_dict_obj_name || ' to USER2';
8 execute immediate sql_stmt;
9 end if;
10 end;
11 /

Trigger created.

SQL> create table test (a varchar2(10));
create table test (a varchar2(10))
*
ERROR at line1:
ORA-00604: error occurred at recursive SQL level 1
ORA-30511: invalid DDL operation in system triggers
ORA-06512: at line 6

Thanks for any help

[santoshym]

Hi

Most DDL operations are not supported in system triggers.I searched thru metalink and this is what Oracle has posted

Error: ORA-30511
Text: invalid DDL operation in system triggers
---------------------------------------------------------------------------
Cause: An attempt was made to perform an invalid DDL operation in a system
trigger. Most DDL operations currently are not supported in system
triggers. The only currently supported DDL operations are table
operations and ALTER?COMPILE operations.
Action: Remove invalid DDL operations in system triggers.

The only operations are possible as this says is DDL table operations and ALTER/COMPILE and grant is not DDL operation

Regards