Skip to Content
avatar image
Former Member

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

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
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Oct 10, 2017 at 05:33 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 09, 2017 at 03:22 AM

    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.

    Add comment
    10|10000 characters needed 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