Limit UPDATE statements to affect only one row

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 Received on Fri Aug 11 2006 - 06:08:50 CDT