DB2 - Problem description
Problem IT18136 | Status: Closed |
INSERT QUERY THAT HAS A COLUMN VALUE GENERATED USING TRIGGER COULD PRODUCE WRONG RESULTS OR SQL0407N | |
product: | |
DB2 FOR LUW / DB2FORLUW / B10 - DB2 | |
Problem description: | |
There is a trigger on the table which will cause the column's value to get generated when the insert statement is executed. The insert query could return a wrong result (not generate values properly) or fail with SQL0407N . Steps to reproduce the SQl0407N error: create table test_null (col1 int not null, col2 int not null, col3 int not null ) IN TPNE_TXN_DT_TBS INDEX IN TPNE_BST_IDX_TBS CREATE OR REPLACE TRIGGER TEST_NULL_B1 NO CASCADE BEFORE INSERT ON TEST_NULL REFERENCING NEW AS NEW FOR EACH ROW BEGIN ATOMIC SET (NEW.col3) = ( SELECT COALESCE(MAX( col3), 0) + 1 FROM test_null WHERE col1 = NEW.col1 AND col2 = NEW.col2) ; END insert into test_null(col1, col2) values (1,1) commit $ db2 "insert into test_null(col1, col2) values (1,1)" DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=3, TABLEID=4, COLNO=2" is not allowed. SQLSTATE=23502 | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 11.1.1 fix pack 2 * **************************************************************** | |
Local Fix: | |
Workaround is to rewrite the trigger. For example: CREATE OR REPLACE TRIGGER TEST_NULL_B1 NO CASCADE BEFORE INSERT ON TEST_NULL REFERENCING NEW AS NEW FOR EACH ROW BEGIN ATOMIC DECLARE temp int; SET temp = ( SELECT COALESCE(MAX( col3), 0) + 1 FROM test_null WHERE col1 = NEW.col1 AND col2 = NEW.col2) ; set new.col3 = coalesce( temp, 1 ); END; | |
available fix packs: | |
Db2 Version 11.1 Mod2 Fix Pack2 iFix001 for Linux, UNIX, and Windows | |
Solution | |
First fixed in DB2 11.1.1 fix pack 2 | |
Workaround | |
not known / see Local fix | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 28.11.2016 16.07.2018 16.07.2018 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |