DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Another problem to create a system trigger

  1. #1
    Join Date
    Mar 2001
    Posts
    109
    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
    zm

  2. #2
    Join Date
    Mar 2001
    Posts
    635
    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
    Santosh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width