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

Home -> Community -> Usenet -> c.d.o.server -> Re: Limit UPDATE statements to affect only one row

Re: Limit UPDATE statements to affect only one row

From: gazzag <gareth_at_jamms.org>
Date: 11 Aug 2006 04:25:48 -0700
Message-ID: <1155295548.044675.240340@75g2000cwc.googlegroups.com>


bernard (bernard_at_bosvark.com) wrote:
> How can I limit UPDATE statements to affect only one row on a table and
> if the update attempts to update more than one row an exception should
> be raised. So to make it cleared it should not affect only the first
> row in a multi row update, the where clause should restrict the update
> to update only one row. I have no control over the SQL submitted and
> therefore can not alter the SQL statement before it is submitted.
>
> Example:
> SQL> --This should work
> SQL> Update emp set sal=0 where empno=7396;
> 1 row updated
> SQL> commit;
>
> SQL> --This should NOT work
> SQL> Update emp set sal=0;
> ORA-20001: You tried to update more than one row, please refrain from
> his.
>
> I tried to use the SQL%ROWCOUNT in a statement level update trigger one
> the SAL column but seems like the attribute does truly one apply to
> implicit cursors managed by PL/SQL
>
> Thanks in advance
> Bernard van Niekerk

Bernard,

Sybrand is absolutely correct. Rather than try to kludge some kind of "fix", you should address your logic and/or business practices.

HTH -g Received on Fri Aug 11 2006 - 06:25:48 CDT

Original text of this message

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