467,209 Members | 1,373 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,209 developers. It's quick & easy.

trigger happy

Why might this trigger cause an 803 error message when only 1 record
insert is ocurring?

If I take the primary key off the FR_HACCP_EOR_RES table the insert
occurs and only inserts 1 record. If I put the primary key back on the
table I get an 803 message when the trigger fires. Note that the
SELECT only gets 1 record .. I verified this. If I leave the key, drop
the trigger and manually do the inserts I get no 803 message.

Please note that I am using the same data inserts into FR_HACCP_EOR
each time and cleaning out the tables each time.

confused,
Julie

CREATE TRIGGER GPMQ.FR_HACCP_EOR AFTER INSERT ON GPMQ.FR_HACCP_RESULTS
REFERENCING NEW AS NEW
FOR EACH ROW MODE DB2SQL
WHEN ( NEW.GCM_ID <> 0 AND GPMQ.IS_HACCP_EOR_READY( NEW.GCM_ID,
NEW.PP_N, NEW.UNIT_ID_N, NEW.EVENT_START_TS ) = 'Y' )
INSERT INTO GPMQ.FR_HACCP_EOR_RES
SELECT A.GCM_ID,
A.PP_N,
A.UNIT_ID_N,
'272' QA_TEST_C,
A.EVENT_END_TS EVENT_START_TS,
A.EVENT_END_TS,
CAST( NULL AS TIMESTAMP ) LST_UPDT_TS
FROM GPMQ.FR_HACCP_RESULTS A
WHERE (EVENT_START_TS) = (
SELECT MAX( EVENT_START_TS )
FROM GPMQ.FR_HACCP_RESULTS B
WHERE A.UNIT_ID_N = B.UNIT_ID_N
AND B.EVENT_END_TS <= NEW.EVENT_START_TS )
AND A.UNIT_ID_N = NEW.UNIT_ID_N;

May 5 '06 #1
  • viewed: 1491
Share:
1 Reply
ju***@charmaxtech.com wrote:
Why might this trigger cause an 803 error message when only 1 record
insert is ocurring?

If I take the primary key off the FR_HACCP_EOR_RES table the insert
occurs and only inserts 1 record. If I put the primary key back on the
table I get an 803 message when the trigger fires. Note that the
SELECT only gets 1 record .. I verified this. If I leave the key, drop
the trigger and manually do the inserts I get no 803 message.

Please note that I am using the same data inserts into FR_HACCP_EOR
each time and cleaning out the tables each time.

confused,
Julie

I suspect that the source of the confusion lies in your understanding of
trigger semantics. Note that the trigger will only start firing after
ALL the rows in the initial insert have been inserted.
Now, as the trigger fires for each row it will see what it did for the
previous row.

One question to ask yourself: Do you want a row trigger or perhaps a
statement trigger?

If that doesn't help we'll need sample data to sort this out.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
May 5 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Trevor Fairchild | last post: by
4 posts views Thread by SAEED BASUDAN | last post: by
8 posts views Thread by Remove the obvious for replies | last post: by
3 posts views Thread by tomtailor@freesurf.fr | last post: by
12 posts views Thread by Bob Stearns | last post: by
7 posts views Thread by Shane | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.