How to use RAISE and CRUD clauses in the same case trigger in SQLite

I recently had to build a trigger for the return management system for an eCommerce website. Essentially we had two tables: RETURN and RETURN_ITEM.

I had to make a trigger on insert on RETURN_ITEM, which raises an error and deletes a RETURN entity.

Let me preface this by saying that it is not a good practice to mix the RAISE clause with other statements. If you need to, then create multiple triggers. But at the same time, this solution might be useful in a variety of other cases.

The trigger looked something like this:

CREATE TRIGGER RETURN_VALIDATION
    BEFORE INSERT ON RETURN_ITEM
        FOR EACH ROW

        BEGIN
        -- The trigger logic
END;

And the solution is as follows:

• Perform the CRUD operations with your trigger check in the WHERE clause

• If its a read operation, then you can just put that in your case with a SELECT 1 FROM … statement

• For everything else, use the changes() API in SQLite to check whether the CRUD operation occurred.

• Create a trigger case on the changes() API.

Like this:

DELETE FROM RETURN
    WHERE ticket_number = NEW.return_ticket_number AND
        NOT EXISTS (
            -- My Condition
        );
        SELECT CASE
            WHEN (SELECT changes() > 0) THEN RAISE(FAIL, 'Error')
    END;
END;