Re: Oracle Delete Trigger - Constrain Delete to 1 Row

From: Frank van Bortel <fvanbortel_at_netscape.net>
Date: Tue, 12 Apr 2005 18:24:05 +0200
Message-ID: <d3gsgj$hi2$1@news3.zwoll1.ov.home.nl>

Ken Denny wrote:
> Very good except for one thing. You never set reccnt back to 0. We want
> them to be only able to delete one row per statement not one row per
> session. Simple fix below.

As the packaged variable(s) get initialized *per transaction*, there's no need:

SQL> delete from employees where manager_id=124; delete from employees where manager_id=124

         *
ERROR at line 1:

ORA-20001: Only 1 delete allowed
ORA-06512: at "HR.APEX_VAR", line 15
ORA-06512: at "HR.BILL_AS", line 2
ORA-04088: error during execution of trigger 'HR.BILL_AS'

+++ Expected behavior.
+++ Now, according to you, this cannot happen, as the

+++ reccnt variable still is over 1 (or else, we would not +++ have the error above, would we?):

SQL> delete from employees where manager_id=124 and rownum=1; 1 row deleted.

SQL> delete from employees where manager_id=124 and rownum=1; 1 row deleted.

SQL> delete from employees where manager_id=124 and rownum=1; 1 row deleted.

SQL> delete from employees where manager_id=124 and rownum=1; 1 row deleted.

SQL> delete from employees where manager_id=124 and rownum=1; 1 row deleted.

SQL> delete from employees where manager_id=124 and rownum=1; 1 row deleted.

SQL> delete from employees where manager_id=124 and rownum=1; 1 row deleted.

SQL> delete from employees where manager_id=124 and rownum=1; 1 row deleted.

SQL> delete from employees where manager_id=124 and rownum=1; 0 rows deleted.

+++ No more people left for this manager....
+++ Salami tactics:
+++  get 'em slice by slice, until you have the salami

-- 
Regards,
Frank van Bortel

Received on Tue Apr 12 2005 - 11:24:05 CDT