Postgresql – How to write the following trigger

postgresqltrigger

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

Best Answer

Raising an exception is not something you do very often. However, if that's your case...

Your trigger function should probably look similar to:

CREATE FUNCTION ensure_consistent_enrolled()
RETURNS trigger AS $$
BEGIN
    IF old.enrolled is NULL THEN
        IF not 
               (old.id in (select id from table1) or
                old.id in (select id from table2) or 
                old.id in (select id from table3) or 
                old.id in (select id from table4) or 
                old.id in (select id from table5) or 
                old.id in (select id from table6)    ) 
        THEN
            RAISE EXCEPTION 'User not enrolled plan'
            USING HINT = 'Ensure user is enrolled in a plan' ;
        END IF ;
    END IF ;

    -- If we didn't raise any exception, return the new row "as is"
    RETURN new ;
END;
$$ LANGUAGE plpgsql;

I am not sure when this trigger should be fired. I assume it should be fired BEFORE an UPDATE. I don't think it makes sense to fire it before/after an INSERT; and don't know at all if it is sensible to trigger also BEFORE DELETE.

If you only need to check this condition when UPDATING, you would have

CREATE TRIGGER check_enrolled 
   BEFORE UPDATE
   ON your_table FOR EACH ROW
   EXECUTE PROCEDURE ensure_consistent_enrolled();
Related Question