Count in Trigger

[c***@hotmail.com]

CREATE TRIGGER DB2INST1.X_BI
NO CASCADE BEFORE
INSERT ON DB2INST1.X
REFERENCING NEW AS n
FOR EACH STATEMENT MODE DB2SQL
WHEN (SELECT COUNT(*)
FROM X1
WHERE XValue = n.XValue > 0)
SIGNAL SQLSTATE '42709'
SET MESSAGE_TEXT = 'Duplicate found';

Cannot use Count in Trigger?
Or My Syntax in WHEN is wrong?
Please advice.
Thanks in Advance.

[Knut Stolze]

Post by c***@hotmail.com
CREATE TRIGGER DB2INST1.X_BI
NO CASCADE BEFORE
INSERT ON DB2INST1.X
REFERENCING NEW AS n
FOR EACH STATEMENT MODE DB2SQL
WHEN (SELECT COUNT(*)
FROM X1
WHERE XValue = n.XValue > 0)
SIGNAL SQLSTATE '42709'
SET MESSAGE_TEXT = 'Duplicate found';
Cannot use Count in Trigger?
Or My Syntax in WHEN is wrong?

It is a small syntax problem. Have a look at the WHERE clause:

WHERE XValue = n.XValue > 0

This is not a valid predicate, of course.

What you have to do is to put parenthesis around such a scalar subselect,
i.e.

WHEN ( (SELECT ... FROM ... WHERE ...) > 0 )