Re: Limit UPDATE statements to affect only one row

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

I'm going to suggest a totally different approach, and yes I know it has a few blemishes, just to open the topic for consideration.

Why not use DBMS_RLS and create a package something like this:

CREATE OR REPLACE PACKAGE BODY upd1row IS

FUNCTION updlimit RETURN VARCHAR2 IS
BEGIN
   RETURN 'rownum = 1';
END updlimit;
END upd1row;
/

And then apply it to everyone.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org