How can I work around a race condition when using INSTEAD OF triggers on a view?

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
Received on Wed Mar 23 2005 - 19:12:17 CST