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: Anurag Varma <avoracle_at_gmail.com>
Date: 23 Jun 2006 11:17:41 -0700
Message-ID: <1151086661.285869.60450@u72g2000cwu.googlegroups.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;
> /

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. Received on Fri Jun 23 2006 - 13:17:41 CDT

Original text of this message

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