Let's say:
CREATE TABLE tableA (
Id TEXT,
Cl INTEGER
... );
I need the following conditions:
a: Both fields, Id and Cl, can be NULL
b: If Id is not null, then the pair Id - Cl must be unique.
I use a TRIGGER for BEFORE INSERT on tableA that works pretty well for the
mentioned condition, but an analogous trigger for BEFORE UPDATE not
behave properly:
CREATE TRIGGER UpdControl BEFORE UPDATE ON tableA
BEGIN
SELECT CASE
WHEN (NEW.Nm IS NOT NULL) AND (NEW.Cl IS NOT NULL) AND
(SELECT Nm FROM tableA WHERE Nm = NEW.Nm AND Cl = NEW.Cl) IS NOT NULL THEN
RAISE(ABORT, 'Duplicated Item.')
WHEN (NEW.Nm IS NOT NULL) AND (NEW.Cl IS NULL) AND
(SELECT Nm FROM tableA WHERE Nm = NEW.Nm AND Cl IS NULL) THEN
RAISE(ABORT, 'Duplicated Item')
END;
END;
Apparently, the problem is that, *under all conditions*, the clauses:
(SELECT Nm FROM tableA WHERE Nm = NEW.Nm AND Cl = NEW.Cl)
(SELECT Nm FROM tableA WHERE Nm = NEW.Nm AND Cl IS NULL)
result in a NOT NULL
Using sqlite3 ver 3.3.7 for Windows (sqlite3.dll).
Any clarification, or idea, in this respect will be grateful.
A. J. Millan
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
