I am writing a trigger that needs to raise an exception if a condition doesn't hold. That condition that should result in a raise is that the current row being modified has a field enrolled
that is null, and the primary key of the row being modified does not appear in one of 6 other tables.
(I know that this is a terrible design, but this is what I've inherited, and I don't have much experience with the more advanced features of our DBMS, postgresql).
I am comfortable with my trigger, but I am having trouble writing the procedure that the trigger should execute: This is what I have so far, which admittedly is not much:
CREATE FUNCTION ensure_consistent_enrolled()
RETURNS trigger AS $$
BEGIN
RAISE EXCEPTION 'User not enrolled plan'
USING HINT = 'Ensure user is enrolled in a plan'
END;
$$ LANGUAGE plpgsql;
What I am trying to achieve in the stored procedure is something like:
enrolled_in_plan = OLD.automatic_payments || !(OLD.id in one of 6 tables)
if <> enrolled_in_plan
raise...
end