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] -----------------------------------------------------------------------------