Search code examples
sqlsqlitetriggerssingle-table-inheritance

Condition in SQLite trigger for inheritance

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. enter image description here 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 ?

Solution

  • 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;