Here is a simple example of a on insert postgresql trigger.
I have a table called
what_did_you_eat. It has the following structure -
what will contain what was eaten.
I have another table called
what_was_the_type with the following structure -
I want to store the type of food that was eaten in
what_type column. And I want to do this to happen on insert into the
So, off we go to write a trigger. Postgresql trigger consists of two parts -
- A trigger
- And a trigger function: Basically a function that is called when trigger is called.
We first write the trigger function.
CREATE OR REPLACE FUNCTION public.trgfunc_populate_the_type() RETURNS trigger LANGUAGE 'plpgsql' COST 100 VOLATILE NOT LEAKPROOF AS $BODY$ DECLARE _type text; BEGIN -- we try to find out if a fruit or a vegetable was eaten -- NEW is the row that was inserted 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 insert the food type INSERT INTO what_was_the_type (what_did_you_eat_id, what_type) SELECT NEW.what_did_you_eat_id, _type; RETURN NULL; END; $BODY$;
Now the trigger.
CREATE TRIGGER trg_populate_type -- for this example, we are interested only in INSERTions AFTER INSERT ON public.what_did_you_eat -- for every row inserted -- we want to execute the trigger function FOR EACH ROW EXECUTE PROCEDURE public.trgfunc_populate_the_type();
INSERT INTO what_did_you_eat(what) VALUES ('apple'); INSERT INTO what_did_you_eat(what) VALUES ('ice cream');
we get the following in the
and in the
what_was_the_type table -
Other postgresql articles