How do I create a trigger on multiple keys on sqlite3

Question

As far as I am aware, there's no direct ability to have foreign key constraints in SQLite 3. I have a many-to-many table that needs this, so I'm creating a trigger that raises an ABORT when the foreign key constraint is violated. My statement looks like this:

CREATE TRIGGER fkFooBar
  BEFORE INSERT ON Foo_Bar
  FOR EACH ROW BEGIN
    SELECT RAISE (ABORT, 'Insert on Foo_Bar violates foreign key')
    WHERE ((SELECT id as fId FROM FOO WHERE fId = NEW.fooId) IS NULL) || ((SELECT id as bId FROM BAR WHERE bId = NEW.barId) IS NULL);
  END;

But this only constrains on the barId being present, not the fooId. I'm only vaguely familiar with SQL, and haven't dealt with triggers before, so I'm a little lost on this. Why doesn't this work? Am I going about this the wrong way? Should this be much simpler? (i.e. in one SELECT statement)

Answer 1

Take the hint.

Triggers are usually a bad idea. You've uncovered yet another reason why triggers are often a mistake.

The primary reason is that triggers break your programming into two pieces. The code that is code -- and is easy to find and maintain -- and the code that is hidden in the database and is much more difficult to find and maintain.

If it's really hard to do, you're using the wrong tool.

Answer 2

Since nobody else actually answered the question I was asking:

|| is not a binary OR in sqlite. Just use a single |