Here\'s an example:

 create table foo (
    id INTEGER NOT NULL PRIMARY KEY
  );

 CREATE TABLE bar (
    id INTEGER NOT NULL PRIMARY KEY,
    foo_id INTEGER NOT NULL
           CONSTRAINT fk_foo_id REFERENCES foo(id) ON DELETE CASCADE
  );

The INSERT trigger looks like this: （插入触发器，当外键为NULL时，回滚）

CREATE TRIGGER fki_bar_foo_id
  BEFORE INSERT ON bar
  FOR EACH ROW BEGIN
      SELECT RAISE(ROLLBACK, \'insert on table "bar" violates foreign key constraint "fk_foo_id"\')
      WHERE  (SELECT id FROM foo WHERE id = NEW.foo_id) IS NULL;
  END;

 

If your foreign key column is not NOT NULL, the trigger\'s WHERE clause needs an extra expression:（如果外键为NULL时，触发器WHERE clause需要一个额外的表达式

 

CREATE TRIGGER fki_bar_foo_id
  BEFORE INSERT ON bar
  FOR EACH ROW BEGIN
      SELECT RAISE(ROLLBACK, \'insert on table "bar" violates foreign key constraint "fk_foo_id"\')
      WHERE  NEW.foo_id IS NOT NULL
             AND (SELECT id FROM foo WHERE id = new.foo_id) IS NULL;
  END;

 

The UPDATE statements are almost identical; if your foreign key column is NOT NULL, then do this:（更新触发器，如果外键为非空时）

 

CREATE TRIGGER fku_bar_foo_id
  BEFORE UPDATE ON bar
  FOR EACH ROW BEGIN
      SELECT RAISE(ROLLBACK, \'update on table "bar" violates foreign key constraint "fk_foo_id"\')
      WHERE  (SELECT id FROM foo WHERE id = NEW.foo_id) IS NULL;
  END;

And if NULLs are allowed, do this:

 

 

CREATE TRIGGER fku_bar_foo_id
  BEFORE UPDATE ON bar
  FOR EACH ROW BEGIN
      SELECT RAISE(ROLLBACK, \'update on table "bar" violates foreign key constraint "fk_foo_id"\')
      WHERE NEW.foo_id IS NOT NULL
            AND (SELECT id FROM foo WHERE id = NEW.foo_id) IS NULL;
  END;

The DELETE trigger is, of course, the reverse of the INSERT and UPDATE triggers, in that it applies to the primary key table, rather than the foreign key table. To wit, in our example, it watches for DELETEs on the foo table:

CREATE TRIGGER fkd_bar_foo_id
  BEFORE DELETE ON foo
  FOR EACH ROW BEGIN
      SELECT RAISE(ROLLBACK, \'delete on table "foo" violates foreign key constraint "fk_foo_id"\')
      WHERE (SELECT foo_id FROM bar WHERE foo_id = OLD.id) IS NOT NULL;
  END;

This trigger will prevent DELETEs on the foo table when there are existing foreign key references in the bar table. This is generally the default behavior in databases with referential integrity enforcement, sometimes specified explicitly as ON DELETE RESTRICT. But sometimes you want the deletes in the primary key table to cascade to the foreign key tables. Such is what our example declaration above specifies, and this is the trigger to do the job:

CREATE TRIGGER fkd_bar_foo_id
  BEFORE DELETE ON foo
  FOR EACH ROW BEGIN
      DELETE from bar WHERE foo_id = OLD.id;
  END;

Pretty simple, eh? The trigger support in SQLite is great for building your own referential integrity checks. Hopefully, these examples will get you started down the path of creating your own.

 

Multiple Column Foreign Keys

I wanted to apply this type of validation to tables with multiple-column foreign keys, but I kept receiving errors like: "only a single result allowed for a select that is part of an expression". This was more due to my inexperience with the syntax, but here is my solution:

 

CREATE TABLE foo (
    id1 TEXT NOT NULL,
    id2 TEXT NOT NULL,
    PRIMARY KEY (id1, id2)
  );

 

CREATE TABLE bar (
    id INTEGER NOT NULL PRIMARY KEY,
    foo_id1 TEXT NOT NULL,
    foo_id2 TEXT NOT NULL,
    FOREIGN KEY (foo_id1, foo_id2) REFERENCES foo (id1, id2)
  );

 

CREATE TRIGGER "fki_bar_foo2ids"
    BEFORE INSERT ON bar
    FOR EACH ROW
    WHEN (SELECT 1 FROM foo WHERE foo.id1 = NEW.foo_id1 AND foo.id2 = NEW.foo_id2) IS NULL
  BEGIN
    SELECT RAISE (ROLLBACK, \'Multiple foreign key mismatch\');
  END;

原文网址：http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers