Writing A On Insert Trigger In Postgresql

programming, snippet

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 what_did_you_eat table.

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();

The results.

On running

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 what_did_you_eat table

what_did_you_eat_id what
1 apple
2 ice cream

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 unknown

Other postgresql articles