|
Problem IC65655
|
Status: Closed |
FOR EACH ROW TRIGGER WITH MERGE STATEMENT MAY PRODUCE INCORRECT BEHAVIOUR. |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
The problem happens when all the conditions below are met:
1. There is a MERGE statement used in CREATE TRIGGER statement
with AFTER and FOR EACH ROW clauses.
2. The MERGE statement in (1) is used to specify an alternate
action of UPDATE when conditions are matched or INSERT
otherwise.
3. The UPDATE and INSERT in (2) modify the same table (referred
thereafter as "target table").
4. Two rows are modified in sequence and the modifications
trigger the TRIGGER statement in (1) in the way that the first
row will
trigger to insert a row to the table in (3) and the second row,
because of having the same value as the first row on the key
column, will trigger to
update the same row that was inserted previously.
The incorrect behaviour is the second row, instead of triggering
to update,triggers another insert to the target table that may
may result in a duplicate key violation sqlcode SQL0803.
This problem was first introduced in DB2 Version 9.1 Fix Pack 6. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* db2v9.7fp1 *
****************************************************************
* PROBLEM DESCRIPTION: *
* FOR EACH ROW TRIGGER WITH MERGE STATEMENT MAY PRODUCE *
* INCORRECT BEHAVIOUR. *
****************************************************************
* RECOMMENDATION: *
* upgrade to db2v9.7fp2 *
**************************************************************** |
| Local Fix: |
The trigger seems to work fine by adding:
"BEGIN and END" clause to the trigger definition. |
| available fix packs: |
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
Fixed in db2v9.7fp2 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 19.01.2010
25.05.2010
25.05.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP2
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.2
|
 |
