trigger puzzlement
Date: Thu, 12 Feb 2004 12:38:14 -0500
Message-ID: <BAOWb.78592$va1.36814_at_fe23.usenetserver.com>
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 Received on Thu Feb 12 2004 - 18:38:14 CET