[sqlite] Trouble with Trigger

[A.J.Millan]

I want know if is possible to do a DELETE, INSERT or UPDATE statement from
inside a TRIGGER.

Let's say:

CREATE TABLE  tableA (
Id  INTEGER,
... );

CREATE TABLE  tableB (
Ref  INTEGER,
... );

CREATE  TRIGGER DeleteReferences BEFORE DELETE ON tableA
BEGIN
   SELECT CASE
   WHEN (SELECT count(*) FROM  tableB  WHERE  Ref  =  OLD.Id) != 0 THEN
             RAISE(ABORT, 'Id field referenced in Table-B')
             // These one Work!!
   WHEN (SELECT count(*)  FROM tableB  WHERE  Ref  =  OLD.Id) != 0 THEN
             DELETE FROM  tableB  WHERE  Ref = OLD.Id
              // I get an error here
  END;
END;

What I want, to avoid future inconsistences, is to erase all the tableB rows
that contains references to the tableA row that will be eliminated.

I obtain same error whichever is the utilized sentence:  DELETE,  INSERT or
UPDATE.

Any idea in this respect, or alternative, will be grateful

A. J. Millan




-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------