| Schema Level Trigger [message #49697] |
Mon, 04 February 2002 21:36  |
vinay
Messages: 27
Registered: December 1999
|
Junior Member |
|
|
Hi all
I want to write a trigger on schema level , which will be fired before dropping any table and ask user for confirmation. If User Says Y then Only table should be dropped.
Clues Please
Thanks
|
|
|
|
|
|
| Re: Schema Level Trigger [message #49707 is a reply to message #49697] |
Tue, 05 February 2002 18:06  |
Manu Gupta
Messages: 44
Registered: January 2002
|
Member |
|
|
Dear Vinay,
Following is the syntex for securing your schema objects from getting dropped. Schema level triggers are available from Oracle 8i onwards.
CREATE OR REPLACE TRIGGER DROP_TABLE1
BEFORE DROP ON SCOTT.SCHEMA
BEGIN
IF DICTINARY_OBJ_OWNER = 'SCOTT'
AND DICTIONARY_OBJ_NAME = 'L%'
AND DICTIONARY_OBJ_TYPE = 'TABLE'
THEN
RAISE_APPLICATION_ERROR
(-20002, 'Table cannot be dropped.');
END IF;
END;
/
I'll let you know if I get the syntex for 'confirming before dropping'.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
