PostgreSQL: plpgsql: trigger on BEFORE UPDATE and INSERT example

Intro: PostgreSQL support triggers. Trigger is a special type of function which is executed BEFORE or AFTER  database row in database table is inserted , updated or deleted.

2 main steps to crate and assign trigger to PostgreSQL table are:
1. Create trigger function
2. Assign trigger function to table and specify when to execute trigger (BEFORE or AFTER events INSERT, UPDATE, DELETE events)

In this example we will create 2 table my_user and my_city, create trigger function and assign it to table my_user. It will be executed on BEFORE INSERT and UPDATE events and will add city to table my_city if it not exists.

Preconditions:
1. pgAdmin III installed
2. PostgreSQL installed

Let's write some code :)

1. Open SQL editor in pgAdmin III
2. Write and execute SQL to create sequencers for our table's id fields:

-- Create sequence for tables id
CREATE SEQUENCE my_user_id_seq START 1000;
CREATE SEQUENCE my_city_id_seq START 1000;

3. Write and execute SQL to create tables:

-- Create tables, and set ID's default values our sequencers
CREATE TABLE my_user(
        id numeric DEFAULT nextval('my_user_id_seq') PRIMARY KEY
    ,    name character varying(255) NOT NULL UNIQUE
    ,    city character varying(255) NOT NULL
);

CREATE TABLE my_city(
        id numeric DEFAULT nextval('my_city_id_seq') PRIMARY KEY
    ,    city character varying(255) NOT NULL UNIQUE
);

4. Create and execute trigger function

CREATE OR REPLACE FUNCTION func_my_user_city_trigger()
  RETURNS trigger AS
$BODY$
DECLARE
    -- declare varyiable
    city_exist int;
BEGIN


    -- TG_OP - is build-in PostgreSQL trigger variable with event type name

    IF  TG_OP='INSERT' OR TG_OP='UPDATE' THEN

    -- NEW - is build-in PostgreSQL trigger variable - it's table row with new data.
    -- OLD - is build-in PostgreSQL trigger variable - it's table row with old data (for UPDATE event)
    -- Please note:
    -- in BEFORE events NEW row NOT equals OLD row (exept data is same).
    -- in AFTER events NEW row equals OLD row.
       
    IF NEW.city IS NOT NULL THEN

    -- Check if city already not exist

    SELECT COUNT(*)
    FROM my_city  AS c
    WHERE  c.city = NEW.city
    INTO city_exist;


        IF city_exist = 0 THEN

            INSERT INTO my_city(city) VALUES(NEW.city);
       
        END IF;

    END IF;
    END IF;

RETURN NEW;
   
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

5. Assign trigger function to table - specify BEFORE | AFTER     INSERT | UPDATE | DELETE.
In our example we will use  BEFORE   INSERT | UPDATE.

 CREATE TRIGGER my_user_city_trigger
  BEFORE INSERT OR UPDATE
  ON my_user
  FOR EACH ROW
  EXECUTE PROCEDURE func_my_user_city_trigger();

Hooray, that's all !!! :)

Now let's do some testing:

1. Write and execute following script:

DELETE FROM my_user;
DELETE FROM my_city;

INSERT INTO my_user(name,city) VALUES( 'Obi-Wan' , 'Kiev' );
INSERT INTO my_user(name,city) VALUES( 'Darth Vader' , 'Moscow' );
INSERT INTO my_user(name,city) VALUES( 'Anakin Skywalker' , 'New York' );


UPDATE my_user SET city = 'Moscow' WHERE name = 'Obi-Wan';
UPDATE my_user SET city = 'Moscow' WHERE name = 'Darth Vader';
UPDATE my_user SET city = 'Beijing' WHERE name = 'Anakin Skywalker';

2. Select all records from my_city,  result should be: