By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,077 Members | 1,356 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,077 IT Pros & Developers. It's quick & easy.

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

P: n/a
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,regnum 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.assoc OR
n.prefix<>o.prefix OR
n.regnum<>o.regnum)
OR
o.regnum='')))
SIGNAL SQLSTATE '75103'
SET MESSAGE_TEXT='Duplicate assoc/prefix/regnum'
GO
May 26 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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,regnum 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.assoc OR
n.prefix<>o.prefix OR
n.regnum<>o.regnum)
OR
o.regnum='')))
SIGNAL SQLSTATE '75103'
SET MESSAGE_TEXT='Duplicate 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(Regnum, Sequence.NextVal))

Then throw the CONSTRAINT on regnum_unique instead of just regnum.

B.

May 26 '06 #2

P: n/a
Please, provide the script to demonstrate your issue. Your design isn't
clear.

May 26 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.