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: sybrandb <sybrandb_at_gmail.com>
Date: 11 Aug 2006 04:23:04 -0700
Message-ID: <1155295384.331832.177110@b28g2000cwb.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

Seems you need to investigate INSTEAD OF triggers. Intended for, but not limited, to views, so maybe you can 'misuse' them for this.

Note: Sooner or later no one will understand what is happening.

-- 
Sybrand Bakker
Senior Oracle DBA
Received on Fri Aug 11 2006 - 06:23:04 CDT

Original text of this message

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