Error to Create a System Trigger
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Error to Create a System Trigger

  1. #1
    Join Date
    Mar 2001
    Posts
    109
    I had trouble to create a system trigger. The following is how that happened.

    SQL> create or replace trigger sys_test
    2 after logon on sys
    3 begin
    4 null;
    5 end;
    6 /
    after logon on sys
    *
    ERROR at line 2:
    ORA-30506: system triggers cannot be based on tables or views.

    By following Oracle documentation, I have run the scripts dbmsstdx.sql and catproc.sql in advance.

    Thanks for any help.
    zm

  2. #2
    Join Date
    Nov 2000
    Posts
    344
    Use this :

    create or replace trigger sys_test
    after logon on database
    begin
    if user = 'SYS' then -- do something
    null;
    end if;
    end;
    /

  3. #3
    Join Date
    Mar 2001
    Posts
    635
    Hi

    First thing is if you execute ur code making a minor change in the code which I done it for you

    create or replace trigger sys_test
    after logon on sys.schema
    begin
    null;
    end;

    this code is fine only change I have made is I have made sys to sys.schema the .schema has to be explictily stated.when you execute the above trigger you will still end with an error it will give you a error stating that

    system trigger cannot be defined on the schema of sys user

    so try the above code as follows on a different user let take scott as the user it will work for example

    create or replace trigger sys_test
    after logon on scott.schema
    begin
    null;
    end;

    Remember .schema is a must

    Regards

    [Edited by santoshym on 05-16-2001 at 02:36 PM]
    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