I need to add a trigger after insert or update on a table so that, where some row is updated and a certain condition is met, then some other table needs to be updated. However, that last update statement may throw an error (updating creates a duplicate index on the table), then I need to take a different approach, meaning I need to delete instead of update.
The function, so far, is this:
CREATE OR REPLACE FUNCTION prod_profiles_check_grouped_item() RETURNS trigger AS $BODY$ DECLARE found_group bigint; BEGIN SELECT group_id INTO found_group FROM inv_items_group_members WHERE item_id = NEW.item_id; IF found_group IS NOT NULL THEN UPDATE public.prod_profiles_steps_items SET item_id = found_group WHERE item_id = NEW.item_id; -- TODO : on error for the last update, this should get executed: -- DELETE FROM public.prod_profiles_steps_items -- WHERE item_id = NEW.item_id; END IF; RETURN NEW; END; $BODY$
How can this be done? All I read everywhere are
RAISE. Which is irrelevant here.