MVSFORUMS.com

[blitz2]

CICS screen has got fields for names (maximum 6 in number with each 5 bytes in length; no duplicates), say,

ABRAH JOHNY JOSHU ROSAK SILLY

They are stored in a DB2 table. The names are part of the primary key. The table has triggers defined for Insert, Delete and Updates to the table. Requirement is such that when the entries are made as say below,

JOYCE JOHNY BILLY ROSAK SILLY SIVAN

Update trigger should be triggered twice (ABRAH<->JOYCE, JOSHU<->BILLY) and the Insert trigger once (SIVAN)

We coded a straight-forward logic to select the old values from the table and do an update with new values .. And extra entries from the screen being inserted .. or if DB2 table contains additional entries than that received from screen, then delete.

Problem happens when circular references are entered on the screen, like,
JOHNY ABRAH ROSAK SILLY BILLY SIVAN

When we try to update ABRAH with JOHNY, it fails with a -803(duplicate). Next same thing happens for JOHNY<->ABRAH, JOSHU<->ROSAK,ROSAK<->SILLY) ... SILLY<->BILLY update and SIVAN insert goes through. Currently, we perform this process 6 times so that all values get updated properly.

Still the problem is that for an update on screen like,
JOHNY ABRAH JOSHU ROSAK SILLY

it never works. Business is adamant that they want ABRAH<->JOHNY and JOHNY<->ABRAH to trigger the update trigger. On the development front, we have a headache now. Because it's always going to get a -803 no matter what you do.

I hope that I have been able to explain the problem properly. Anybody faced anything like this before. Any suggestions?

the triggers insert rows in another table from which an audit report with old and new values are generated. So that is why we are after the UPDATE command rather than a DELETE and then an INSERT
________
no2 vaporizer

[kolusu]

blitz2,

I think you are complicating by performing multiple updates/insert/delete sequence. Updating a primary key is only possible if you have a stand alone table.

1. Read the screen entries
2. Check if the row exists
3. Sort both the table entry and screen entries as the column sequence ascending.

[blitz2]

Kolusu, thanks for the response.
________
volcano vaporizers