Re: Question: Use Trigger to Prevent Deletes with no Where Clause?

From: Noel <tomekb_at_s_o_f_t_m_a_n.pl>
Date: Fri, 14 Jan 2005 14:00:32 +0100
Message-ID: <cs8g01$sha$1@inews.gazeta.pl>

Użytkownik Thomas Kyte napisał:

> In article <1105644268.227713.55120_at_f14g2000cwb.googlegroups.com>, Jesse says...
>

>>Hi all.  We recently discovered a bug in one of our applications (it's
>>in Visual Basic; we inherited it) that under an obscure set of
>>conditions executes a "DELETE FROM [table]" without a where clause.  We
>>can fix the app, but getting it certified for use takes time; however,
>>I can make various administrative changes to the database however
>>without approval.
>>
>>My question is, can I use a "before" trigger to detect the mass "DELETE
>>FROM" statement and prevent it from executing?
>>
>>Thanks.
>>Jesse

> No db version, bummer.....
> guessing: you are using software written this century, this'll work in 9i and
> up:
> ops$tkyte_at_ORA9IR2> create table t ( x int );
>
> Table created.
>
> ops$tkyte_at_ORA9IR2> create or replace trigger t_trigger
> 2 before delete on t
> 3 declare
> 4 l_sql_text ora_name_list_t;
> 5 l_n number;
> 6 l_found boolean := false;
> 7 begin
> 8 l_n := ora_sql_txt(l_sql_text) ;
> 9 for i in 1 .. l_n
> 10 loop
> 11 l_found := upper(l_sql_text(i)) like '%WHERE%';
> 12 exit when l_found;
> 13 end loop;
> 14 if ( not l_found )
> 15 then
> 16 raise_application_error( -20000, 'you totally lose' );
> 17 end if;
> 18 end;
> 19 /
>
> Trigger created.
>
> ops$tkyte_at_ORA9IR2>
> ops$tkyte_at_ORA9IR2> delete from t where x > 5;
>
> 0 rows deleted.
>
> ops$tkyte_at_ORA9IR2> delete from t;
> delete from t
> *
> ERROR at line 1:
> ORA-20000: you totally lose
> ORA-06512: at "OPS$TKYTE.T_TRIGGER", line 14
> ORA-04088: error during execution of trigger 'OPS$TKYTE.T_TRIGGER'
>
>
> In 8i, maybe you could "count rows" using a before trigger to set a package
> variable to 0, a row trigger to count rows deleted -- raising an application
> error when "more than you feel should be deleted at a time" are.
>
> Funny you can make new bugs in the database at will, but you cannot fix existing
> bugs in application without lots of effort.
>
>

Both solution aren't good.

I can put always-true condition in WHERE clause and i delete all rows. You slow delete if you try to count rows don't you?

What about this:

  create or replace trigger t_trigger
   after delete on t

      declare
             l_n        number;
     begin
      select count(*)
       into l_n
        from t;
       if l_n = 0
           then
                raise_application_error( -20000, 'you still totally lose' );
             end if;

  end;
  /

-- 
Noel

Received on Fri Jan 14 2005 - 07:00:32 CST