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.

First time with MySQL !

Everybody will have a day when they decide to try something which they knew for a long time. That day is very unique, they will try their best, seek help from many, refer to many things and finally it ends up in merry when they succeed in running it. I have been learning SQL for the past 50 days but I could not have a hands on experience till today. I decided to somehow run SQL today, and the reason I am on FC4 made me to go in for MySQL. My Window’ian friends have always been complaining that I am becoming more into Open source softwares / Linux there days, and to add fire into that burning fuel I wanted to successfully run MySQL :DI began the endeavor by first installing MySQL server using the Add/Remove programs option. After successfully installing MySQL, I searched the Main Menu for a MySQL option. Not finding them there ( I know where to go now ) I opened up the terminal and typed ‘mysql’. To make me smile, MySQL started up and showed me ‘ mysql> ‘ prompt. As I assumed to have successfully got into MySQL, i tried my first command straight away !

mysql > CREATE TABLE PDATA (NAME CHAR(10), PHONENUM CHAR(10));

Like hitting me in the head, it said ” Error: No database found”. I resolved to use the help to find something

mysql> help

The help gave me many commands but there wasn’t one how to create a database. Luckily I had my LinuxGuru on line, so IM’d him to find what to do. He said I have to create a database using,

$ mysqladmin create data_base

It created the corresponding database “data_base” and gave me the result ” Query OK, 0 rows selected (0.01 sec) “. I again assumed the process has ended and I can go on with creating tables, issuing the CREATE TABLE statement. To my surprise again got an error message but this time as ” Permission denied to access database as @localhost”.

This time he suggested me to refer the MySQL site for help. Then I created the required permission for me to login as parth@localhost and access the MySQL server. Then I again tried to login as user, but still wasn’t able to as it needed a slightly different command. Finally, after again referring to the MySQL Site I was able to finally start MySQL as user and create tables and insert data into it.

The entire procedure for a first-time-run of MySQL is as follows,

1. Loging in as ‘root’ or obtaining root permission with ’su’, issue the following command at shell,

[root@localhost]$ mysql –user=root mysql

mysql > GRANT ALL PRIVILEGES ON *.* TO ‘user_name’@'localhost’
IDENTIFIED BY ‘passwd’ WITH GRANT OPTION;

This assigns ‘all’ privileges to the user ‘user_name@localhost’ who can be identified and can access mysql using the password ‘passwd’.

2. We have to create a database for the user to use, by issuing the following command as root ( or su ),

[root@localhost]$ mysqladmin create user_database

which will give the reply as ” Query OK, 0 rows affected (0,01 sec) ” on successful creation of the database.

3. Login as user by issuing the following command in the shell,

[user_name@localhost]$ mysql –user=user_name –password=passwd

The welcome text will indicate your successful login: “Welcome to MySQL Monitor.” and you will get the ‘mysql’ prompt.

4. Now, we have to change the database into ours by issuing the following command,

mysql > USE user_dbase

which will be confirmed with “Database changed”.

5. Now, you can issue the MySQL statements at the ‘mysql’ prompt and your ‘user-dbase’ will be the used database.

To Create Table : mysql > CREATE TABLE PDATA (NAME CHAR(10), PHONENUM CHAR(10));To add data into the table : mysql > INSERT INTO PDATA (NAME , PHONENUM) VALUES ( Name , 9880098800 );

To view the table : mysql > SELECT * FROM PDATA;