trigger puzzlement

[Mike Stenzler]

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

[Kenneth Koenraadt]

Hi Mike,

Please post the exact ORA- error message next time.

I tried to recreate, and I got the error below, which I belive you got
too. But it has nothing to do with locks.

ORA-01403: no data found
ORA-06512: ved "A.AUROW_GRID_CLONE", linje 6
ORA-04088: fejl under udf°relse af triggeren 'A.AUROW_GRID_CLONE'

Which means that your intitial check returns no row => no data found
to put into v_exists => exception is raised. This is the expected
behaviour.

What you need to do is to catch that exception and handle it
appropriately. You could also make your check more readable (and even
prettier), like this :

begin
select 1
into dummy_var
from test.t2
where trunc(last_date) = trunc(sysdate); --today
and cmdy = :new.cmdy_sym;

-- No exception, There was exactly one row, do the update here
exception
when no_data_found then
-- there was no record for today, do the insert here.
when too_many_rows then
-- Something went terribly wrong !
raise;
end;
- Kenneth Koenraadt



On Thu, 12 Feb 2004 14:52:18 -0500, "Mike Stenzler"
<ms*******@ssaris.com> 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

[Mike Stenzler]

<Kenneth Koenraadt> wrote in message
news:40**************@news.inet.tele.dk...

Hi Mike,

I tried to recreate, and I got the error below, which I belive you got
too. But it has nothing to do with locks.
Ken- thanks for the reply. Initially I was using Q&E to perform the updates
that fired the trigger. It would come back and complain about the row being
updated by someone else... Then I got smart(er) and use SQL+ to fire the
trigger. SQL+ gives me the same 3 errors you received, starting with the
1403.
ORA-01403: no data found
ORA-06512: ved "A.AUROW_GRID_CLONE", linje 6
ORA-04088: fejl under udf°relse af triggeren 'A.AUROW_GRID_CLONE' Which means that your intitial check returns no row => no data found
to put into v_exists => exception is raised. This is the expected
behaviour.

What you need to do is to catch that exception and handle it
appropriately.
That's what I'm starting to realise. I was under the mistaken impression
that the use of the EXISTS clause would return existance or non-existance of
the requested data without raising an error condition.
You could also make your check more readable (and even
prettier), like this :

begin
select 1
into dummy_var
from test.t2
where trunc(last_date) = trunc(sysdate); --today
and cmdy = :new.cmdy_sym;

-- No exception, There was exactly one row, do the update here
exception
when no_data_found then
-- there was no record for today, do the insert here.
when too_many_rows then
-- Something went terribly wrong !
raise;
end;

Thanks - I'll try your version!

Mike

[Mike Stenzler]

Ken-

Ttried your method and works fine, plus is much cleaner.

Thanks so much for the help.

-Mike

[Serge Rielau]

If you are on O9i try the MERGE statement.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab