Re: trigger puzzlement

[Mike Stenzler]

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_GRI D_CLONE", line 8
ORA-04088: error during execution of trigger 'TEST'.AUROW_GR ID_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" <ms*******@ssar is.comwrote in message
news:BA******** ***********@fe2 3.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_G RID_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]

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" <ms*******@ssar is.comwrote in message
news:M_******** *******@fe08.us enetserver.com. ..

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_GRI D_CLONE", line 8
ORA-04088: error during execution of trigger 'TEST'.AUROW_GR ID_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" <ms*******@ssar is.comwrote in message
news:BA******** ***********@fe2 3.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_G RID_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

[Raj Jamadagni]

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_chan ge) = Trunc(SYSDATE)
and cmdy = :new.cmdy_sym);
exception
when no_data_found then
v_exists := 'FALSE';
when too_many_rows then
raise_applicati on_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