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

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 13 Jan 2005 11:36:57 -0800
Message-ID: <115645017.00009d7d.070@drn.newsguy.com>

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.

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation

Received on Thu Jan 13 2005 - 13:36:57 CST