DevOps, CI/CD and Automation

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