Re: how to prevent drop on a specific table

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 23 Jun 2006 11:10:31 -0700
Message-ID: <1151086231.717893@bubbleator.drizzle.com>

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;
> /

Thanks.

Consider:

IF ora_dict_obj_name = 'P_TEST' THEN and eliminate the SELECT INTO.

-- 
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 Fri Jun 23 2006 - 13:10:31 CDT