Forum Stats

  • 3,874,160 Users
  • 2,266,675 Discussions
  • 7,911,745 Comments

Discussions

createing system trigger

ITZ_DBA
ITZ_DBA Member Posts: 30
edited Apr 21, 2011 7:20AM in Designer
Hi,

I need to create a trigger to grant the select role and create synonym to that table which will fires after table is created.


SQL> CREATE OR REPLACE TRIGGER ddl_create_alter
2 AFTER CREATE or ALTER ON SCHEMA
3 BEGIN
4 IF ora_dict_obj_type ='TABLE' THEN
5 Execute immediate 'grant select on ' ||ora_dict_obj_name||' to SYSTEM';
6 EXECUTE IMMEDIATE 'create or replace synonym SYSTEM.'||ora_dict_obj_name||' for SYSTEM'||'.'|| ora_dict_obj_name;
7 END IF;
8 END;
9 /

Trigger created.

SQL> create table test(v char(2));
create table test(v char(2))
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-30511: invalid DDL operation in system triggers
ORA-06512: at line 3


Please can any one help me out how to overcome from this error

Edited by: 847356 on Mar 25, 2011 4:49 AM
Tagged:

Answers

  • jflack
    jflack Member Posts: 1,524 Bronze Trophy
    I think you're posting this in the wrong forum - this is the Oracle Designer forum.

    Post in the SQL and PL/SQL forum: 3077
  • ITZ_DBA
    ITZ_DBA Member Posts: 30
    hey,

    I want to know which forum is best of my request can any one help.... plz.
  • 846947
    846947 Member Posts: 27
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:663613799256

    http://asktom.oracle.com/pls/asktom/f?p=100:11:4431232071443862::::P11_QUESTION_ID:646423863863

    create or replace trigger t1_trigger
    after CREATE on sunil.schema
    declare
    l_str varchar2(255);
    l_job number;
    begin
    if ( ora_dict_obj_type = 'TABLE' )
    then
    l_str := 'execute immediate "grant select on ' ||
    ora_dict_obj_name ||
    ' to hr";';
    dbms_job.submit( l_job, replace(l_str,'"','''') );
    end if;
    end;
    /
    Thanks,
    Sunil
This discussion has been closed.