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;