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

In article <cs8g01$sha$1_at_inews.gazeta.pl>, Noel says...
>
>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.
>>>

...
>Both solution aren't good.
>

first, OP said clearly:

>>>conditions executes a "DELETE FROM [table]" without a where clause. We

                                               ^^^^^^^^^^^^^^^^^^^^^^

they had a specific question, to which -- there was a specific answer.

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

so? is data integrity/protection

1. MORE important than performance at all costs
2. LESS important than performance at all costs

You choose -- it is a rather binary decision. No data, or slightly slower data.

>
>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;
> /

Oh, now -- you want to talk SPEED? "count(*)" after each delete?

I'll take approach

1. look for where clause (answers the question)

over

b) count rows as they are deleted, when threshold hit -- stop. no one would even notice this for 10's of rows, which is the most likely situation.

over

c) count every row in the table every time we delete 0, 1 or more rows.

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