Using a trigger to enforce unique if not null DB2 v8.1.9 linux

[Bob Stearns]

I have successfully designed a trigger to make sure a set of fields are
unique if not null. However it fails in one case: when the duplication
occurs among a set of rows being updated simultaneously. The trigger is
below. Can you suggest a way to improve it to eliminate the failure? I
don't know enough about triggers to know if changing BEFORE UPDATE to
AFTER UPDATE, for instance, might do what I want. This is only my 5th
trigger.

BTW: Is the column list in the OF phrase an 'or' or an 'and' between the
columns?

CREATE TRIGGER IS3.AN_apr_UNQ_ U
NO CASCADE BEFORE UPDATE OF assoc,prefix,re gnum ON is3.animals
REFERENCING NEW AS N OLD AS O
FOR EACH ROW MODE DB2SQL
WHEN (n.regnum<>''
AND EXISTS(SELECT * FROM is3.animals
WHERE assoc=N.assoc
AND prefix=n.prefix
AND regnum=n.regnum
AND ((n.assoc<>o.as soc OR
n.prefix<>o.pre fix OR
n.regnum<>o.reg num)
OR
o.regnum='')))
SIGNAL SQLSTATE '75103'
SET MESSAGE_TEXT='D uplicate assoc/prefix/regnum'
GO

[Brian Tkatch]

Bob Stearns wrote:

I have successfully designed a trigger to make sure a set of fields are
unique if not null. However it fails in one case: when the duplication
occurs among a set of rows being updated simultaneously. The trigger is
below. Can you suggest a way to improve it to eliminate the failure? I
don't know enough about triggers to know if changing BEFORE UPDATE to
AFTER UPDATE, for instance, might do what I want. This is only my 5th
trigger.

BTW: Is the column list in the OF phrase an 'or' or an 'and' between the
columns?

CREATE TRIGGER IS3.AN_apr_UNQ_ U
NO CASCADE BEFORE UPDATE OF assoc,prefix,re gnum ON is3.animals
REFERENCING NEW AS N OLD AS O
FOR EACH ROW MODE DB2SQL
WHEN (n.regnum<>''
AND EXISTS(SELECT * FROM is3.animals
WHERE assoc=N.assoc
AND prefix=n.prefix
AND regnum=n.regnum
AND ((n.assoc<>o.as soc OR
n.prefix<>o.pre fix OR
n.regnum<>o.reg num)
OR
o.regnum='')))
SIGNAL SQLSTATE '75103'
SET MESSAGE_TEXT='D uplicate assoc/prefix/regnum'
GO

I don't know enought abou TRIGGERs either.

I would think, however, that this does not need to be done in a
TRIGGER. Rather, a GENERATED COLUMN could do.

I had a similar problem. That, i had two COLUMNs, where the first was
NOT NULL, the second could be NULL. But, the second could be NULL only
once per value of the first. So, i added a third COLUMN, GENERATED
ALWAYS AS (COALESCE(Col2, (Col1 || '-'))), and put a UNIQUE INDEX on
that.

In your case, the NULL is causing problems. However, if NULL was a
unique value it would not be. To generate unique values a SEQUENCE
could be used, and allow it when the value IS NULL.

regnum_unique GENERATED ALWAYS AS (COALESCE(Regnu m, Sequence.NextVa l))

Then throw the CONSTRAINT on regnum_unique instead of just regnum.

B.

[Konstantin Andreev]

Please, provide the script to demonstrate your issue. Your design isn't
clear.