Re: DDL Trigger question

"Rudy Susanto" <rsusanto_at_atlas-sp.com> wrote in message news:1a928d0b.0207300030.4d450a18_at_posting.google.com...
> Hi,
>
> How to prevent my objects from deletion? I think I can use DDL trigger
> but i found a problem. Here is the script.
>
> SQL>connect scott/tiger
> Connected.
>
> SQL> create trigger tr_drop
> 2 before drop on schema
> 3 begin
> 4 raise_application_error(-20000,'drop object may not allow...');
> 5 end;
> 6 /
> Trigger created.
>
> SQL> create table emp2 as select * from emp where 1=2;
> Table created.
>
> SQL> drop table emp2;
> drop table emp2
> *
> ERROR at line 1:
> ORA-00604: error occurred at recursive SQL level 1
> ORA-20000: drop object may not allow...
> ORA-06512: at line 2
>
> SQL> conn system/manager
> Connected.
>
> SQL> drop table scott.emp2;
> Table dropped.
> SQL>
>
> When I connect as SCOTT, I can't drop the table but when I connect as
> SYSTEM, I can drop it easily. Why this trigger was't running as i
> expected?
>
> This trigger works on schema level, is it possible if i want to know
> which object that user want to delete?
>
> I am using Oracle 8.1.7 NT.
>
>
> Thank in advance,
>
> Rudy Susanto

As I understand it, Oracle have decided that database triggers must not fail if the user has the 'burned in' DBA role. This, apparently, is to protect us from ourselves, as otherwise we could create, for example, a logon trigger which would lock everybody out from the database forever. They may have good reason, but if it were up to me I would exempt only SYS as SYSDBA.

My 2c.

Regards,
Paul Received on Wed Jul 31 2002 - 14:30:44 CDT