Home » SQL & PL/SQL » SQL & PL/SQL » DDL Trigger not working (Oracle 11.1.0.7)
DDL Trigger not working [message #584489] Tue, 14 May 2013 16:10 Go to next message
deepa_balu
Messages: 73
Registered: March 2005
Member
I have a trigger to automate grants to a Role whenever a table is created in my schema.I have dba rights.

CREATE OR REPLACE PROCEDURE deepa.myddl
(p_ddl IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE p_ddl;
END;
/

CREATE OR REPLACE TRIGGER deepa.gfc_grant
AFTER CREATE ON deepa.schema
DECLARE
l_jobno NUMBER;
l1_jobno NUMBER;
BEGIN
IF ora_dict_obj_type = 'TABLE' THEN
dbms_job.submit(l_jobno,'myddl(''GRANT SELECT ON '||ora_dict_obj_owner||'.'||ora_dict_obj_name||' TO deepa_ACCESS'');');
dbms_job.submit(l1_jobno,'myddl(''GRANT INSERT,UPDATE,DELETE ON '||ora_dict_obj_owner||'.'||ora_dict_obj_name||' TO deepa_ACCESS'');');
END IF;
END;
/



This works perfectly when i create a table as deepa user.
But say when another User creates a table in my schema

like create table deepa.test1 ( a number) This trigger is not fired.
Whenever a table is created by me or by any other user in my Schema, i need the trigger to be fired.
Please help.
Re: DDL Trigger not working [message #584491 is a reply to message #584489] Tue, 14 May 2013 17:04 Go to previous messageGo to next message
BlackSwan
Messages: 23160
Registered: January 2009
Senior Member
EXEC DEEPA.MYDDL('DROP DATABASE');

Not every problem can or should have a technical solution!

The whole approach is fatally flawed.
Application object should be created by static SQL that only gets run at/during application version upgrade activity.
Re: DDL Trigger not working [message #584575 is a reply to message #584489] Wed, 15 May 2013 13:38 Go to previous message
LKBrwn_DBA
Messages: 449
Registered: July 2003
Location: WPB, FL
Senior Member
This is Oracle standard operation:
Quote:Oracle® Database PL/SQL Language Reference
SCHEMA Triggers

A SCHEMA trigger is created on a schema and fires whenever the user who owns it is the current user and initiates the triggering event.

Suppose that both user1 and user2 own schema triggers, and user1 invokes a DR unit owned by user2. Inside the DR unit, user2 is the current user. Therefore, if the DR unit initiates the triggering event of a schema trigger that user2 owns, then that trigger fires. However, if the DR unit initiates the triggering event of a schema trigger that user1 owns, then that trigger does not fire.

Perhaps you could create a Database trigger and trap it.

[Updated on: Wed, 15 May 2013 13:45] by Moderator

Report message to a moderator

Previous Topic: Funzione Sha-256
Next Topic: Help in SQL
Goto Forum:
  


Current Time: Mon Dec 22 09:04:10 CST 2014

Total time taken to generate the page: 0.07736 seconds