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