By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,924 Members | 1,727 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,924 IT Pros & Developers. It's quick & easy.

trigger happy

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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.