Re: Limit UPDATE statements to affect only one row

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Fri, 11 Aug 2006 15:23:29 +0200
Message-ID: <44dc8674$0$20029$9b4e6d93@newsspool4.arcor-online.net>

bernard (bernard_at_bosvark.com) schrieb:
> Can not misuse instead of trigger on table
> ORA:25002: cannot create INSTEAD OF triggers on tables
> Version:10.2.0.1
>
> So a view it will be (nope is can't be used I'm told) since it breaks
> Hibernate (aaaaargh!!!!!!) which discovers the data relationships
> through foreign keys on tables to effectively generate the code
> (ouch!).
>
> Any more ideas are welcome.
>
> Regards
> Bernard
>
> P.S. Fight the use Hibernate in all circumstances unless you want to
> limit the functionality of Oracle by 80% since Hibernate attempts to
> create database independence (yea right).
>

May be not too elegant, but seems to work.

scott_at_ORA102> CREATE OR REPLACE PACKAGE emp_pck

   2 IS
   3 counter PLS_INTEGER := 0;
   4 END;
   5 /

Package created.

scott_at_ORA102>
scott_at_ORA102> CREATE OR REPLACE TRIGGER before_emp

   2 BEFORE UPDATE ON emp
   3 FOR EACH ROW
   4 DECLARE
   5 More_Than_One EXCEPTION;
   6 PRAGMA EXCEPTION_INIT(More_Than_One, -20001);    7 BEGIN
   8 IF Emp_Pck.Counter = 0 THEN
   9 Emp_Pck.Counter := Emp_Pck.Counter + 1;   10 ELSE
  11 RAISE More_Than_One;
  12 END IF;
  13 EXCEPTION
  14 WHEN More_Than_One THEN

  15      Emp_Pck.Counter := 0;
  16      Raise_Application_Error(-20001, 'You updated more than 1 row');

  17 END;
  18 /

Trigger created.

scott_at_ORA102> CREATE OR REPLACE TRIGGER After_Emp

   2 AFTER UPDATE ON Emp
   3 BEGIN
   4 Emp_Pck.Counter := 0;
   5 END;
   6 /

Trigger created.

scott_at_ORA102> UPDATE EMP SET SAL=SAL WHERE EMPNO=7782;

1 row updated.

scott_at_ORA102>
scott_at_ORA102> UPDATE EMP SET SAL=SAL WHERE EMPNO=7566;

1 row updated.

scott_at_ORA102>
scott_at_ORA102> UPDATE EMP SET SAL=SAL WHERE EMPNO=7782 OR EMPNO=7566; UPDATE EMP SET SAL=SAL WHERE EMPNO=7782 OR EMPNO=7566         *
ERROR at line 1:

ORA-20001: You updated more than 1 row
ORA-06512: at "SCOTT.BEFORE_EMP", line 13
ORA-04088: error during execution of trigger 'SCOTT.BEFORE_EMP'

scott_at_ORA102>
scott_at_ORA102> UPDATE EMP SET SAL=SAL WHERE EMPNO=7566;

1 row updated.

Best regards

Maxim Received on Fri Aug 11 2006 - 08:23:29 CDT