I have created a before update trigger that I would like to use to prevent null values in a column that has unique values. In case this value is not submitted at the insertion I would like the trigger to write in that column the ID value which is the primary key of the table. Both columns are of the same type - bigint

CREATE TRIGGER TRIGG_1 BEFORE INSERT ON MY_TABLE
REFERENCING NEW AS new_row FOR EACH ROW MODE DB2SQL
WHEN ( new_row.UNIQUE_FIELD IS NULL )
BEGIN ATOMIC
SET new_row.UNIQUE_FIELD = new_row.P_ID;
END

It doesnt work and I have no idea why. I tried to put a "SIGNAL" statement into the "WHEN" section to see if it isnt being skipped and I actually got the signal message after running the insert query when the UNIQUE_VALUE was null...

It is a DB2 Express-c database
DB2version: "DB2 v9.7.100.177", "s091114", "IP23028", and Fix Pack "1".

I will be very grateful for any help...

Define "doesn't work".

Sorry

I don't get any errors... The entry is written to the database and the UNIQUE_VALUE column is NULL...

This way when I have two entries with NULL in the UNIQUE_VALUE column the insertion fails, because of the unique constraint on this column...

P_ID wouldn't be an IDENTITY column by any chance?

Andy

Hmm, no...

It is not generated as an identity.

I migrated from PostgreSQL to DB2 using the IBM Migration tool... It created the ddl automatically...

Maybe this could cause the problem:
(it is from the ddl file after the create statement which is ok.... but I'm not sure about this ALTER statement... I actually don't know what it does...

ALTER TABLE MY_TABLE
DATA CAPTURE NONE
PCTFREE 0
LOCKSIZE ROW
APPEND OFF
NOT VOLATILE
DEACTIVATE VALUE COMPRESSION
COMPRESS NO
DROP RESTRICT ON DROP;

Can you post the DDL of the table and the insert statement that you are using?

Andy

Here is the creation DDL.. I removed the unique constraint.

The column 'UNIQUE_VALUE' is the column CNUM

An example of an insert query which should put 1234567890 into the column CNUM:
insert into "PUBLIC".cp2_contact(P_ID,NAME, SURNAME) VALUES (1234567890, 'Bla', 'Bla');

CREATE TABLE CONTACT (
P_ID BIGINT NOT NULL,
NAME VARCHAR(120),
SURNAME VARCHAR(120),
SEX VARCHAR(4),
DBIRTH DATE,
STREETNO VARCHAR(20),
STREET VARCHAR(254),
ZIP VARCHAR(10),
CITY VARCHAR(40),
CNUM BIGINT,
CNTRY VARCHAR(60),
PHONE VARCHAR(30),
GSM VARCHAR(30),

CONSTRAINT PK_CONTACT
PRIMARY KEY (P_ID)
)
DATA CAPTURE NONE
IN TS8;

ALTER TABLE CONTACT
DATA CAPTURE NONE
PCTFREE 0
LOCKSIZE ROW
APPEND OFF
NOT VOLATILE
DEACTIVATE VALUE COMPRESSION
COMPRESS NO
DROP RESTRICT ON DROP;

CREATE INDEX PUBLIC.IX10_CONTACT
ON CONTACT
(NAME, SURNAME)
PAGE SPLIT SYMMETRIC
COMPRESS NO
ALLOW REVERSE SCANS;

CREATE INDEX PUBLIC.IX11_CONTACT
ON CONTACT
(NAME, STREETNO, SURNAME, ZIP)
PAGE SPLIT SYMMETRIC
COMPRESS NO
ALLOW REVERSE SCANS;

CREATE INDEX PUBLIC.IX12_CONTACT
ON CONTACT
(NAME, SURNAME, ZIP)
PAGE SPLIT SYMMETRIC
COMPRESS NO
ALLOW REVERSE SCANS;

I am not sure what the problem is but try this:

The -1 should be unnecessary, but put it in for now to see what happens.

Andy

GREAT! It works... Thank you very much!!! You made my day