Writing a On Delete Trigger in Postgresql

programming, snippet

The previous [article] ( /posts/writing-a-on-insert-trigger-in-postgresql/) was about insertion trigger.

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 delete a row from what_did_you_eat, I would not want a row to remain in what_was_the_type to refer to a non existing row in the what_did_you_eat table. To handle this, I will use the delete trigger.

Like before we first write the trigger function.

CREATE OR REPLACE FUNCTION public.trgfunc_delete_the_type()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
	DECLARE _id_to_delete int;
    BEGIN
    
        -- we try to find out which row was deleted
        -- OLD is the row that was deleted
        _id_to_delete = OLD.what_did_you_eat_id;
        -- we now delete the linked row 
        DELETE FROM what_was_the_type WHERE what_did_you_eat_id = _id_to_delete;
    
        RETURN NULL;
    END;
$BODY$;

Now the trigger.

CREATE TRIGGER trg_delete_type
    -- for this example, we are interested only in INSERTions
    AFTER DELETE
    ON public.what_did_you_eat
    -- for every row DELETED
    -- we want to execute the trigger function
    FOR EACH ROW
    EXECUTE PROCEDURE public.trgfunc_delete_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

DELETE FROM what_did_you_eat WHERE what ='apple';

we get the following in the what_did_you_eat table

what_did_you_eat_id what
2 ice cream

and in the what_was_the_type table -

what_was_the_type_id what_did_you_eat_id what_type
2 2 unknown

The trigger deleted the row which had what_was_the_type_id = 1.

Other postgresql articles