Re: Limit UPDATE statements to affect only one row

DA Morgan wrote:
> RETURN 'rownum = 1';

Daniel

My apologies, I was not clear on my initial intent and requirement: The business logic is to only allow update statements that intend to update only one record in a table and not allow (at all) any update statements that attempts to update 2 or more records in a table. The use for such a requirement might be questionable but at least one usage might be (1) in a development environment where developers should not update more than one row at a time on a table (I know there are other ways to prevent this) and (2) where a security policy dictates this requirement (my case). This preventative requirement is imposed to prevent all SQL that attempts to update more than one record in a table.

The use of 'rownum' would allow the update still to take place on the first row of the targeted data which will be (don't know if this is still correct) based on the physical storage of the data in the datafile if no 'order by' clause is used in a sub query on the update. Frequent execution of the SQL trying to update more than one row; with a solution based on 'rownum', might eventually succeed in updating all the targeted records if the where clause is well structured to exclude already updated records.

Regards
Bernard van Niekerk Received on Tue Aug 15 2006 - 03:52:54 CDT