Writing a On Delete Trigger in Postgresql
The previous [article] ( /posts/writing-a-on-insert-trigger-in-postgresql/) was about insertion trigger.
I had two tables
what_was_the_type. On insertion into
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
DELETE FROM what_did_you_eat WHERE what ='apple';
we get the following in the
and in the
what_was_the_type table -
The trigger deleted the row which had
what_was_the_type_id = 1.
Other postgresql articles