Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle Delete Trigger - Constrain Delete to 1 Row

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US