Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: how to prevent drop on a specific table

Re: how to prevent drop on a specific table

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 26 Jun 2006 15:32:40 -0700
Message-ID: <1151361163.635394@bubbleator.drizzle.com>


Anurag Varma wrote:
> PMDORAIS_at_gmail.com wrote:

>> DA Morgan a écrit :
>>
>>> PMDORAIS_at_gmail.com wrote:
>>>> HI, i'm not a big wiz with oracle but i would like to prevent dropping
>>>> a specfic table only for all users.
>>>>
>>>> How can i do that??  ps. i use Oracle9i Enterprise Edition Release
>>>> 9.2.0.5.0 .
>>>>
>>>>
>>>> Thanks!!!!
>>> There are code examples of how to prevent this with DDL Event
>>> Triggers in Morgan's Library at www.psoug.org. Look under DDL
>>> Event Triggers.
>>>
>>> Also look at the same technique for preventing TRUNCATE and ALTER.
>>> --
>>> Daniel A. Morgan
>>> University of Washington
>>> damorgan_at_x.washington.edu
>>> (replace x with u to respond)
>>> Puget Sound Oracle Users Group
>>> www.psoug.org
>>
>> *****
>>
>> WHAT A GREAT SITE!!!  Thanks a lot!!!  Works like a charm!!  here is my
>> script, if someone needs to do the same:
>>
>> CREATE OR REPLACE TRIGGER DBM_NODROP
>> BEFORE DROP
>> ON SCHEMA
>>
>> DECLARE
>>  x user_tables.table_name%TYPE;
>> BEGIN
>>   SELECT ora_dict_obj_name
>>   INTO x
>>   FROM dual;
>>
>>   IF x ='P_TEST' THEN
>>     RAISE_APPLICATION_ERROR(-XXXXX, 'Table Names Can Not
>>     BE DELETED');
>>   END IF;
>> END no_xtabs;
>> /

>
> ORA92> create table p_test (a number);
>
> Table created.
>
> ORA92> drop table p_test;
> drop table p_test
> *
> ERROR at line 1:
> ORA-00604: error occurred at recursive SQL level 1
> ORA-20001: Table Names Can Not
> BE DELETED
> ORA-06512: at line 8
>
>
> ORA92> rename p_test to b_test;
>
> Table renamed.
>
> ORA92> drop table b_test;
>
> Table dropped.
>
> ORA92> create table p_test (a number);
>
> Table created.
>
> ORA92> alter trigger DBM_NODROP disable;
>
> Trigger altered.
>
> ORA92> drop table p_test;
>
> Table dropped.

Change the trigger to:

CREATE OR REPLACE TRIGGER DBM_NODROP
BEFORE ALTER OR DROP OR GRANT OR RENAME OR REVOKE OR TRUNCATE ON SCHEMA Next! ;-)

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Mon Jun 26 2006 - 17:32:40 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US