Re: trigger puzzlement

Mike Stenzler wrote:
>
> This may be obvious, but I don't write many triggers so it's got me
> puzzled..
>
> Row level, after update trigger is designed to perform an insert or update
> to a 2nd table based on existance of a record in the 2nd table.
>
> t1 is updated and has trigger, whenever an update happens, it inserts or
> updates a record in t2. If there has already been an insert "today" (using
> SYSDATE w/ timestamp stripped out) we perform an update on t2, otherwise we
> insert.
>
> Problem is code works fine if we comment out the check for existance and
> just insert records. code works OK if there is an existing record and we
> perform an update. However - if we check for existance and there is no
> record, instead of inserting, we come up with an error saying we can't
> perform an update - another process has a lock.
>
> DATA
>
> CREATE TABLE TEST.T1
> (
> CMDY_SYM VARCHAR2(6)
> ,ZONE1 NUMBER(6,2)
> );
>
> CREATE TABLE TEST.T2
> (
> LAST_CHANGE DATE,
> CMDY VARCHAR2(6)
> ,GV NUMBER(6,2)
> );
>
> code:
>
> "TEST"."AUROW_GRID_CLONE" AFTER
> UPDATE OF "ZONE1" ON "TEST"."T1" FOR EACH ROW
>
> declare v_exists VARCHAR2(6) :='FALSE';
>
> BEGIN
>
> -- test if record has already been inserted today
> select 'TRUE' into v_exists from DUAL where EXISTS
> (select * from test.t2
> where last_change = TO_DATE(TO_CHAR(SYSDATE(), 'MM-DD-YYYY'),'MM-DD-YYYY')
> and cmdy = :new.cmdy_sym
> );
>
> if (v_exists = 'TRUE') then
> update test.t2
> set gv = :new.zone1
> where last_change = TO_DATE(TO_CHAR(SYSDATE(),
> 'MM-DD-YYYY'),'MM-DD-YYYY')
> and cmdy = :new.cmdy_sym;
> else
> insert into test.t2
> ( last_change, cmdy, gv )
> values
> (
> TO_DATE(TO_CHAR(SYSDATE(),'MM-DD-YYYY'),'MM-DD-YYYY'),
> :new.cmdy_sym,
> :new.zone1
> );
> end if;
>
> END;
>
> Any ideas as to whart I'm doing wrong?
>
> Oracle 9.2.0.3
>
> Mike

First thing I'm thinking is a possible data corruption:

Session 1:                 Session 2:

update t1                  update t1
trigger                    trigger
- sees no rows in t2       - sees no rows (since session1 not committed)
- does insert              - does insert

Hmmmmmmmm...

Some suitable primary/unique keys will at least block session 2, but you need to check the multi-user implications as well as the single user implications.

hth
connor

-- 
-------------------------------
Connor McDonald
http://www.oracledba.co.uk
Co-Author: "Mastering Oracle PL/SQL - Practical Solutions"