Re: how to prevent drop on a specific table

From: <PMDORAIS_at_gmail.com>
Date: 23 Jun 2006 11:39:08 -0700
Message-ID: <1151087948.237932.196300@p79g2000cwp.googlegroups.com>

Anurag Varma a écrit :

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

---

i know... but i just wanted to protect the table to be dropped by inadvertency....

so it is fine with me!!! Received on Fri Jun 23 2006 - 13:39:08 CDT