• Welcome to SQLitening Support Forum.
 

News:

Welcome to the SQLitening support forums!

Main Menu

more fun with triggers - modifying ON DELETE CASCADE

Started by Bern Ertl, February 19, 2009, 08:38:48 PM

Previous topic - Next topic

Bern Ertl

Consider the following example:CREATE TABLE T1 (T1F1, T1F2);

CREATE TABLE T2 (T2F1 INTEGER CONSTRAINT fk_T1_RowID REFERENCES T1(RowID) ON DELETE CASCADE, T2F2);

-- Foreign Key Preventing insert
CREATE TRIGGER fki_T2_T2F1_T1_RowID
BEFORE INSERT ON [T2]
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'insert on table "T2" violates foreign key constraint "fki_T2_T2F1_T1_RowID"')
  WHERE NEW.T2F1 IS NOT NULL AND (SELECT RowID FROM T1 WHERE RowID = NEW.T2F1) IS NULL;
END;

-- Foreign key preventing update
CREATE TRIGGER fku_T2_T2F1_T1_RowID
BEFORE UPDATE ON [T2]
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'update on table "T2" violates foreign key constraint "fku_T2_T2F1_T1_RowID"')
      WHERE NEW.T2F1 IS NOT NULL AND (SELECT RowID FROM T1 WHERE RowID = NEW.T2F1) IS NULL;
END;

-- Cascading Delete
CREATE TRIGGER fkdc_T2_T2F1_T1_RowID
BEFORE DELETE ON T1
FOR EACH ROW BEGIN
    DELETE FROM T2 WHERE T2.T2F1 = OLD.RowID;
END;


What I'd like to do is modify the last trigger so that, instead of deleting rows in T2 where T2F1 matches the RowID of any record I'm deleting in T1, it simply sets the T2F1 field to NULL.  Any ideas if this is possible or how I might accomplish this?

Bern Ertl

D'oh!  I just saw the CREATE TRIGGER definition on the SQLite site.  If I'm reading it right, the following should work:

-- Cascading Delete
CREATE TRIGGER fkdc_T2_T2F1_T1_RowID
BEFORE DELETE ON T1
FOR EACH ROW BEGIN
    UPDATE T2 SET T2F1 = NULL WHERE T2.T2F1 = OLD.RowID;
END;