Triggers in PostgreSQL – Quick Example!

I tried my first hand on PostgreSQL Triggers yesterday. Actually my current project needs some triggers, but I decided to try with a test table and create a trigger for it.

There are two tables, ‘addressbook’ and ‘phonebook’. The ‘addressbook’ is the main table in which data is to be entered. The structure of ‘addressbook’ is as follows.

Table "public.addressbook"
Column  |          Type          |                        Modifiers
----------+------------------------+----------------------------------------------------------
id       | integer                | not null default nextval('addressbook_id_seq'::regclass)
name     | character varying(100) |
address1 | character varying(100) |
address2 | character varying(100) |
address3 | character varying(100) |
phonenum | character varying(15)  |
Indexes:
"addressbook_pkey" PRIMARY KEY, btree (id)
"addressbook_name_key" UNIQUE, btree (name)

The ‘phonebook’ is another table with just stores ‘name’ and ‘phonenum’ fields of the ‘addressbook’. The structure of ‘phonebook’ table is,

Table "public.phonebook"
Column  |          Type          |                       Modifiers
----------+------------------------+--------------------------------------------------------
id       | integer                | not null default nextval('phonebook_id_seq'::regclass)
name     | character varying(100) |
phonenum | character varying(15)  |
Indexes:
"phonebook_pkey" PRIMARY KEY, btree (id)

Now, when a data is inserted into the ‘addressbook’ table, the ‘name’ and ‘phonebook’ values being inserted should also be inserted into the corresponding fields of ‘phonebook’ as well. For each insertion into the ‘addressbook’ table, there should be a corresponding insertion into the ‘phonebook’ table, which is accomplished using a trigger given below.


DROP TRIGGER phonebook on addressbook;
CREATE LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION add_to_phonebook() RETURNS TRIGGER AS $phonebook$
DECLARE
new_name varchar;
new_phonenum varchar;
BEGIN
IF(TG_OP='INSERT') THEN
INSERT INTO phonebook(name,phonenum) VALUES(NEW.name,NEW.phonenum);
END IF;
RETURN NEW;
END;
$phonebook$ LANGUAGE plpgsql;
CREATE TRIGGER phonebook AFTER INSERT ON addressbook FOR EACH ROW EXECUTE PROCEDURE add_to_phonebook();

This trigger function is named add_to_phonebook(). The SQL statement between BEGIN and END is the actual trigger code.

Let us now check the code by inserting data into the ‘addressbook’ table and check whether the data was inserted into the ‘phonebook’ table or not.

test=# INSERT INTO addressbook (name,address1,address2,address3,phonenum) VALUES ('Peter','Flat #2','3rd Street','Chennai','9842498424');
INSERT 0 1
test=# select * from phonebook;
id  |  name   |  phonenum
------+---------+------------
4025 | Peter | 9842498424
(1 row)

Yes, the trigger has worked. Now, an insertion of a row into ‘addressbook’ will cause insertion of corresponding data into the ‘phonebook’ as well. :)

About these ads

31 thoughts on “Triggers in PostgreSQL – Quick Example!

  1. thanks a lot for the clarity and the elegance of the code.

    I have a quite similar problem and needed some advice on how to write a trigger for a heirarchical tables/data foreign keys–

    I have these tables:
    family (family_id, family_name),
    genus (genus_id, genus_name, family_id)
    species(species_id, species_name, genus_id, family_id)

    I want to write a trigger to ensure that the family_id stored in species table is always correctly aligned with that found by looking for the family via the genus table.

    Any counsel would be very much appreciated and properly referred.

  2. I have a question, couse when im doing an trigger on INSERT, my table that activates the trigger dublicates the INSERT and i get two rows instead of 1..

    What could couse this?

    • Please explain how you write trigger.

      Its quite interesting to see whether you have where condition in insert !!!!

    • Actually trigger has to be called inside table(the table you want to perform updates) when you are doing dml opertion in java or other programs.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s