trigger puzzlement
From: Mike Stenzler <mstenzler_at_ssaris.com>
Date: Thu, 12 Feb 2004 14:52:18 -0500
Message-ID: <yPQWb.1$1E.0_at_fe20.usenetserver.com>
This may be obvious, but I don't write many triggers so it's got me puzzled..
else
end if;
Date: Thu, 12 Feb 2004 14:52:18 -0500
Message-ID: <yPQWb.1$1E.0_at_fe20.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
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 - 20:52:18 CET