I want to create a table that contains a few fields, but two of them are supposed to be equal (at their role) and those two are the primary key.
The table is supposed to contain those fields: AGREEMENT1, AGREEMENT2, TYPE, DESCRIPTION.
The primary key is built from the 2 agreement fields. My problem is that the 2 following rows are equal by me, and I dont want the possibility of the two of them exist at the same time:
A unique index on Agreement1, Agreement2 won't be enough here.
Is there any way to do that thing?
The code should be something like:
CREATE TRIGGER trigger_name before insert ON table_name REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL WHEN (N.A, N.B IN (SELECT B, A FROM table_name)) SIGNAL SQLSTATE '75000' ('Error: Agreements are the same')
Try repaire the above code to work for you.
You will probably have to write the "before update" trigger also.