BEFORE DDL Trigger [message #409616] |
Tue, 23 June 2009 05:41 |
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 |
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.
|
|
|
|