Re: Concurrency problem with INSTEAD OF trigger

<smauldin_at_ingrian.com> wrote in message news:1112051490.464059.301130_at_l41g2000cwc.googlegroups.com...
>I have a table called TEST_TRIG and a view on the table called
> TEST_TRIG_VIEW.
> I created an INSTEAD OF trigger on TEST_TRIG_VIEW to modify the table
> and other info
> when the view is updated. If two update statements from two different
> session execute an
> update that will modify a column in the where clause I see unexpected
> results. The second
> statement executes using the old column value in the where clause.
>
> statement 1: update TEST_TRIG_VIEW set EMPNO = 1111 where EMPNO = 1;
>
> statement 2: update TEST_TRIG_VIEW set EMPNO = 9999 where EMPNO = 1;
>
> commit statement 1
> commit statement 2
> I would expect statement 2 not to update any rows because statement
> changed the
> EMPNO value from 1 to 1111. This problem does not occur when performed
> on the table directly.
>
> --create table, view, and instead of trigger
> create table TEST_TRIG (EMPNO number(4), ENAME varchar2(10));
>
> insert into TEST_TRIG(EMPNO, ENAME) values (1, 'Billy');
> insert into TEST_TRIG(EMPNO, ENAME) values (2, 'Johnny');
> insert into TEST_TRIG(EMPNO, ENAME) values (3, 'Sammy');
>
> create or replace view TEST_TRIG_VIEW(EMPNO,ENAME,THE_ROW_ID)
> as
> select EMPNO, ENAME, ROWID
> from TEST_TRIG;
>
> create or replace trigger TEST_TRIG_UPD_TRIG instead of update on
> TEST_TRIG_VIEW
> referencing NEW as NEW OLD as OLD
> for each row
> begin
> update TEST_TRIG set EMPNO = :NEW.EMPNO, ENAME = :NEW.ENAME
> where ROWID = :OLD.THE_ROW_ID;
> end;
> /
>
> -- Test from sqlplus session
> -- sqlplus session 1
> update TEST_TRIG_VIEW set EMPNO = 1111 where EMPNO = 1;
>
> -- sqlplus session 2
> update TEST_TRIG_VIEW set EMPNO = 9999 where EMPNO = 1;
>
> -- sqlplus session 1
> commit;
>
> -- sqlplus session 2
> commit;
>
> -- select to get the value of EMPNO
> select * from TEST_TRIG_VIEW;
>
> -Stephen
>

two different session, each getting a read consistent view. You have got the 'lost update' syndrome here.

If you want to stop other people touching the record do select .... for update Received on Mon Mar 28 2005 - 17:22:19 CST