OraFAQ Forum: Server Administration » Schema Level Trigger

Home » RDBMS Server » Server Administration » Schema Level Trigger

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: Schema Level Trigger [message #49703 is a reply to message #49697]

Tue, 05 February 2002 03:28

Suresh Vemulapalli
Messages: 624
Registered: August 2000

Senior Member

it is not possible

Report message to a moderator

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'.

Report message to a moderator

Re: Schema Level Trigger [message #49750 is a reply to message #49707]

Thu, 07 February 2002 11:13

andrew again
Messages: 2577
Registered: March 2000

Senior Member

A trigger is pl/sql and pl/sql can't pause and receive interactive input, so I don't know of any way you could do it.

Report message to a moderator

Previous Topic:	my sessions are frequently locked
Next Topic:	repair archive-log-files

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Mar 15 18:31:27 CDT 2025