Condition in SQLite trigger for inheritance

Question

I'm working with SQLite,I am using XOR single table inheritance, I want to create a trigger that enables me to:

1. Check before insertion if the InstructionRefs.id is already created in the table RideHeightRefs

2. Ckeck before insertion that the InstructionRefs.id does not exist in the other inherited table StrappingRefs. I took some oracle PL/SQL code and changed it, I guess I am writing it wrong starting from IF NOT EXISTS (SELECT id...):

   CREATE TRIGGER IF NOT EXISTS insert_instructionRefs_trigger BEFORE INSERT ON InstructionRefs
BEGIN
IF NOT EXISTS (SELECT id FROM RideHeightRefs AS RHR INNER JOIN InstructionRefs IR ON RHR.id = IR.id)
BEGIN
SELECT RAISE(FAIL, '"RideHeightRefs" key is unknown. Insertion in "instructionRefs" is impossible.')
END'
IF NOT EXISTS (SELECT * FROM (SELECT RideHeightRefs FROM StrappingRefs UNION ALL SELECT RideHeightRefs FROM InstructionRefs) T WHERE RideHeightRefs IN (SELECT RideHeightRefs FROM NEW)) BEGIN SELECT RAISE(FAIL, '"RideHeightRefs" key is used in another table. Insertion in "StrappingRefs" is impossible.') END END

How can I modify the code to make it compatible with sqlite syntax ?

Answer 1

To check that the corresponding row in the base table exists, just use a foreign key constraint.

SQLite has no IF statement. To check for something, add a WHERE clause to the SELECT FAIL, or use the trigger's WHEN clause:

CREATE TRIGGER IF NOT EXISTS insert_instructionRefs_trigger
BEFORE INSERT ON InstructionRefs
WHEN EXISTS (SELECT *
             FROM StrappingRefs
             WHERE id = NEW.id)
BEGIN
  SELECT RAISE(FAIL, '"RideHeightRefs" key is used in another table. Insertion in "StrappingRefs" is impossible.');
END;