Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: how to prevent drop on a specific table
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; >> /
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.orgReceived on Mon Jun 26 2006 - 17:32:40 CDT