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

In article <csggqh$od8$1_at_inews.gazeta.pl>, Noel says...
>
>Użytkownik Thomas Kyte napisał:
>
>> In article <cs8g01$sha$1_at_inews.gazeta.pl>, Noel says...
>>
>>>Użytkownik Thomas Kyte napisał:
>> Oh, now -- you want to talk SPEED? "count(*)" after each delete?
>>
>> I'll take approach
>>
>> a) 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.
>>
>Hello Thomas.
>
>I didn't check previosly, but now i did.
>As i suspect trigger with count(*) will fire ONCE for statetment, so
>your interpretation of c is wrong.
>

No it is not -- my interpretation is definitetly not wrong.

I said

"count every row in the table every time we delete 0, 1 or more rows"

That is precisely what happens.

I delete zero rows:
delete from t where 1=0;
you will count each and every row.

I delete precisely zero or one row:
delete from t where rownum = 1;
You will count each and every row.

I delete all rows:
delete from t where 1=1;
you will once again, issue a select count(*) from T; attempting to count each and every row.

I stand by my answer that A, B, C in that order would be my preference.

Think about it -- EVERY TIME someone does a delete, you will issue a select count(*) from T. Every time -- each and every time.

You would discover that to be hugely expensive as compared to counting rows as you delete them and aborting the delete when you hit some threshold.

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