Writing A On Update Trigger in Postgresql

programming, snippet

The previous [article] ( /posts/writing-a-on-delete-trigger-in-postgresql/) was about deletion trigger. This article is about update triggers.

I had two tables what_did_you_eat and what_was_the_type. On insertion into what_did_you_eat, the what_type column was populated with the food type.

Now, if I update the value in what column of a row from what_did_you_eat, I would want the corresponding row in what_was_the_type table to be updated too. For example, if we have the following data:

what_did_you_eat_id what
1 apple
2 ice cream
what_was_the_type_id what_did_you_eat_id what_type
1 1 fruit
2 2 unknown

And I updated ice cream with potato in what_did_you_eat table , then I would want unkown to be updated with vegetable in the what_was_the_type table. One of the ways this can be done is by using a trigger on update.

Like before we first write the trigger function.

CREATE OR REPLACE FUNCTION public.trgfunc_update_the_type()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
    DECLARE _id_to_update int;
    DECLARE _type text;
    BEGIN
    
        -- we try to find out which row was updated
        -- NEW is the row that was updated
        _id_to_update = NEW.what_did_you_eat_id;
        IF (NEW.what = 'apple' or NEW.what = 'orange') THEN
            _type = 'fruit';
        ELSEIF (NEW.what = 'potato' or NEW.what = 'carrot') THEN
            _type = 'vegetable';
        ELSE
            -- if we can't determine the type of food, we mark it
            -- as unkown
            _type = 'unknown';
        END IF;
        -- we now update the linked row 
        UPDATE what_was_the_type SET what_type = _type
        WHERE what_did_you_eat_id = _id_to_update;
    
        RETURN NULL;
    END;
$BODY$;

Now the trigger.

CREATE TRIGGER trg_update_type
    -- for this example, we are interested only in INSERTions
    AFTER DELETE
    ON public.what_did_you_eat
    -- for every row UPDATE
    -- we want to execute the trigger function
    FOR EACH ROW
    EXECUTE PROCEDURE public.trgfunc_update_the_type();

What are the results? From the [insert trigger article] ( /posts/writing-a-on-insert-trigger-in-postgresql/) , we have the following data in the what_did_you_eat and what_was_the_type tables.

what_did_you_eat_id what
1 apple
2 ice cream
what_was_the_type_id what_did_you_eat_id what_type
1 1 fruit
2 2 unknown

On running

UPDATE what_did_you_eat SET what = 'potato' WHERE what_did_you_eat_id = 2;

we get the following in the what_did_you_eat table

what_did_you_eat_id what
1 apple
2 potato

and in the what_was_the_type table -

what_was_the_type_id what_did_you_eat_id what_type
1 1 fruit
2 2 vegetable

The trigger updated the row which had what_was_the_type_id = 2.

Other postgresql articles