• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

Let Oracle Trigger do the work

I need to create a trigger to create the synonyms when any new table is created.

Currently; this is a manual task as below:-
Create created (say Test). Create table test a(number);

After that the manual tasks as below:-
create public synonym test for test;
grant select,insert,delete on test to emp_role;

I need help in automate the process using trigger. ( The trigger should create the synonym for the new objects and also grant privileges to roles).
0
Oranew
Asked:
Oranew
1 Solution
 
Wasim Akram ShaikCommented:
You directly cannot use grants in triggers, you may have to use Pragma and call it via a procedure..

first create the procedure and afterwards create the trigger

Try something like this, this is an extract from the oracle forums website, https://community.oracle.com/thread/402826

create or replace TRIGGER TRIG_CREATE_TABLE
AFTER CREATE ON SCHEMA
begin
  if ora_dict_obj_type='TABLE' then
    grant_privs (ora_dict_obj_owner, ora_dict_obj_name);
  end if;
end;

create or replace procedure grant_privs (p_owner in varchar2, p_tablename in varchar2) authid current_user
is
   pragma autonomous_transaction;
begin
   execute immediate 'grant select, insert, delete, update on ' || p_owner ||'.'|| p_tablename ||' to role_crud_mtd';
execute immediate 'create public synonym '||p_tablename||' for '||p_table_name;

   commit;
end grant_privs;
0
 
OranewAuthor Commented:
Thank you so much.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now