TRIGGER WHILE DROPPING A TABLE in Oracle 10G R2

hc2342uhxx3vw36x96hq
hc2342uhxx3vw36x96hq used Ask the Experts™
on
Is there any way, in Oracle 10G R2 PL/SQL, to create a TRIGGER that fires when DROPping a particular table?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
CREATE OR REPLACE TRIGGER PreventDropTable
BEFORE DROP ON scott.SCHEMA
BEGIN
   IF DICTIONARY_OBJ_TYPE = 'TABLE' THEN
      RAISE_APPLICATION_ERROR(-20202,'No drop allowed on '
         ||DICTIONARY_OBJ_OWNER||'.'||DICTIONARY_OBJ_NAME);
   END IF;
END;
/

tryt this..
ORA-30506: system triggers cannot be based on tables or views
How can I fix this?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

You're right shru_0409, I've substituted the SCHEMA keyword with the table name... (my error).
I would like to write a trigger that, before dropping a particular table ALPHA, drops all the remaining tables.
Any other clues?!
Top Expert 2009

Commented:
That trigger will be:

1) Dangerous
2) Hard to write, since it will be recursively calling itself by dropping another table (which will trigger again).

Why not just use PL/SQL or SQL to generate the drop statements on the fly?

Don't worry about hazard of that trigger, help me to write it.
UP
Top Expert 2009

Commented:
>>Don't worry about hazard of that trigger, help me to write it.

Good luck.
Thanks! ;-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial