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.