Home » SQL & PL/SQL » SQL & PL/SQL » BEFORE DDL Trigger (Oracle10g)
BEFORE DDL Trigger [message #409616] Tue, 23 June 2009 05:41 Go to next message
alok1301
Messages: 1
Registered: June 2009
Location: India
Junior Member
Hi,

Problem Description:
I have two schemas [X,Y]. Now I want to create a trigger to perform the below task:
1. Whenever X tries to create any table, table should be created in Y schema.
2. Whenever X tries to drop a table, table should be dropped from Y schema.

For some audit purposes, we can't create any table in X schema.All tables should be created in Y schema but I will execute create command in X schema due to some application setup.

Now I have some initial idea that a "before create on database" trigger can solve this problem but I have no idea how to create object in 'Y' schema?

CREATE OR REPLACE TRIGGER trg_before_ddl
BEFORE CREATE ON DATABASE
IF ( ora_login_user = 'X') THEN
<Create object in 'Y' schema>
END IF;
END;
/

Can anyone suggest how we can do this?

Regards,
Alok
Re: BEFORE DDL Trigger [message #409618 is a reply to message #409616] Tue, 23 June 2009 05:59 Go to previous messageGo to next message
cookiemonster
Messages: 13952
Registered: September 2008
Location: Rainy Manchester
Senior Member
You'd have to use dynamic SQL if it's possible at all.
But I really, really wouldn't do this. It's going to be monumentally confusing if it works at all.
Just sort your systems out so people create objects direct in schema Y.
Re: BEFORE DDL Trigger [message #409620 is a reply to message #409616] Tue, 23 June 2009 06:02 Go to previous message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Create a trigger on the schema not on database.

Regards
Michel
Previous Topic: it is too urgent how to return the primarykey number as string
Next Topic: external table
Goto Forum:
  


Current Time: Sat Nov 09 20:07:44 CST 2024