Skip to Content
0

How to set a value when column is NULL with a BEFORE INSERT trigger?

Sep 14, 2017 at 02:12 PM

342

avatar image
Former Member

I try to automatically increment a column when the value specified in the INSERT is zero, NULL or just omitted.

I am close to a solution but can someone explain why the last INSERT omitting the column fails?

CREATE TABLE tab1 ( pk INTEGER NOT NULL, name VARCHAR(100) );
CREATE SEQUENCE tab1_sqs START WITH 1 NO CACHE;
CREATE TRIGGER tab1_stg BEFORE INSERT ON tab1 REFERENCING NEW ROW newrow FOR EACH ROW BEGIN DECLARE nv BIGINT = NULL; IF ( :newrow.pk=0 OR :newrow.pk IS NULL ) THEN SELECT tab1_sqs.nextval INTO nv FROM DUMMY; newrow.pk = :nv; END IF; END;
INSERT INTO tab1 VALUES ( NULL, 'aaa' );
INSERT INTO tab1 VALUES (    0, 'bbb' );
SELECT * FROM tab1 ORDER BY name;
INSERT INTO tab1 (name) VALUES ('ccc');       /* fails! Why? */

Output:

0 rows affected (overall time 2742 usec; server time 1391 usec)
0 rows affected (overall time 2283 usec; server time 1098 usec)
0 rows affected (overall time 4669 usec; server time 2055 usec)
1 row affected (overall time 29.581 msec; server time 3641 usec)
1 row affected (overall time 8180 usec; server time 1337 usec)
PK,NAME
1,"aaa"
2,"bbb"
2 rows selected (overall time 5990 usec; server time 156 usec)
* 287: cannot insert NULL or update to NULL: PK SQLSTATE: HY000
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Tae Suk Son
Oct 10, 2017 at 05:33 AM
1

Thank you for reporting this issue. Both "INSERT INTO tab1 VALUES(NULL,'aaa')" and "INSERT INTO tab1 (name)VALUES('ccc')" should return the same outcome, so this will be treated as a bug.

Share
10 |10000 characters needed characters left characters exceeded
Lars Breddemann
Oct 09, 2017 at 03:22 AM
1

This one is surprising and a bit tricky. I could not find a clear description of how to handle this scenario (execution of check constraint before a BEFORE TRIGGER for unspecified columns).

FWIW, I can offer a workaround that can help out here:

CREATE TABLE tab1 (pk INTEGER NOT NULL DEFAULT 0, name VARCHAR(100));

With this, your last INSERT statement works just fine.

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thank you all for considering this. Sorry for the late answer.

Until it's fixed, I will try the suggestion from Lars.

I think it can do the job, as we control the way the table is created, and will remain valid even after the fix.

FYI, it's to emulate an Informix SERIAL type. We translated to a native SAP HANA SQL.

Should be possible to change:

colname SERIAL     =>   colname INTEGER NOT NULL

To:

colname SERIAL     =>   colname INTEGER NOT NULL DEFAULT 0

Tx!
Seb

0