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

Sorry- left out the complete error info:

when I attempt to execute from SQL+ I get the following 3 errors:


ORA-01403: no data found
ORA-06512: at "TEST.AUROW_GRID_CLONE", line 8
ORA-04088: error during execution of trigger 'TEST'.AUROW_GRID_CLONE'

this highlights that the select for pre-existance of "today's" t2.record is
causing a problem when there is no record. I was under the impression that
using the EXISTS statement was a way to query a table w/o getting a
SQL_NOTFOUND returned as an ERROR value.

Do I have to somehow turn off error processing before this query. In Pro*C I
can do this with the
EXEC SQL WHENEVER ERROR CONTINUE:

ideas?



"Mike Stenzler" <mstenzler (AT) ssaris (DOT) com> wrote

One other thought -

should I be writing an error handler for the 1403 condition? Or is there a
way to phrase this query so one doesn't get an error?

Thanks

Mike

"Mike Stenzler" <mstenzler (AT) ssaris (DOT) com> wrote

"Mike Stenzler" <mstenzler (AT) ssaris (DOT) com> wrote


There are several ways to do this, without using a cursor, or
error-handling.

select count(*) into v_count from test.t2 where ...;
if v_count > 0 then ...

or if you really want to use exists:

select (select 'TRUE' from dual where exists (...) union select 'FALSE' from
dual where NOT exists (...))
into v_exists from dual;
if v_exists = 'TRUE' then ...

There are probably more ways to do this, but these should get you going.

Theo

Assuming you have only one row in t2 for each date ... your problem
might be just because of date and times ...

Try ...

BEGIN
-- test if record has already been inserted today
begin
select 'TRUE'
into v_exists
from DUAL
where EXISTS
(select *
from test.t2
where trunc(last_change) = Trunc(SYSDATE)
and cmdy = :new.cmdy_sym);
exception
when no_data_found then
v_exists := 'FALSE';
when too_many_rows then
raise_application_error('20001','Multiple rows found !!');
when others then
raise;
end;
--
if (v_exists = 'TRUE') then
update test.t2
set gv = :new.zone1
where last_change = trunc(SYSDATE)
and cmdy = :new.cmdy_sym;
else
insert into test.t2 ( last_change, cmdy, gv )
values (trunc(sysdate), :new.cmdy_sym, :new.zone1 );
end if;
END;
/

HTH
Raj