Database trigger in PostgreSQL

Triggers in DB provides a facility to set triggers on certain tables for performing events (insert, delete, update) in another table. For instance, I want if any entry added to table A, consequentially a new row will be added to table B with some information that comes from table A. This process also is applicable for deletion and update.

Each database has its own configuration to setup triggers. In PostgreSQL setting up triggers are fairly easy. Basically, just need to create a trigger and a stored procedure in order to get the job done.

An example is provided in the following section for better understanding about triggers and how it works.

Assume there is a table in the database called BETA_TBL. The target is to set two triggers (insert, delete) on BETA_TBL to update a newly created table called BETA_TRIGGER.

In order to achieve this, first of all, we need to create BETA_TRIGGER_TBL table like this,

CREATE TABLE IF NOT EXISTS beta_trigger_tbl (beta_tbl_pkid integer NOT NULL,status varchar(50), ins_time timestamp);

Now it is time to create the insertion trigger for our example. To do so, need to have a trigger like below,

CREATE TRIGGER beta_trigger_insert AFTER INSERT ON beta_tbl FOR EACH ROW EXECUTE PROCEDURE beta_insert_func();

As you can see, the trigger calls a function (beta_insert_func) which does insertion to the table BETA_TRIGGER_TBL. The code for stored procedure is demonstrated in below,

CREATE OR REPLACE FUNCTION beta_insert_func() RETURNS TRIGGER AS $example_table$
BEGIN
INSERT INTO beta_trigger_tbl(beta_tbl_pkid, status, inc_time)
VALUES (new.pkid, 'UNPROCESSED', current_timestamp);
RETURN NEW;
END;
$example_table$ LANGUAGE plpgsql;

Now lets move on to the deletion trigger. The trigger code for deletion on BETA_TBL table is like following,

CREATE TRIGGER beta_trigger_delete BEFORE DELETE ON beta_tbl
FOR EACH ROW EXECUTE PROCEDURE beta_delete_func();

As you can see, there is obvious difference between insertion and deletion trigger. In the first, the word after is used because, the pkid of the new entity that inserted to BETA table is required as a reference in BETA_TRIGGER table. However, for deletion this procedure is reversed since first database should delete BETA_TRIGGER entry before deleting the master record.

If we change the word before to after, the behavior of the database will be changed. In this case, first the master record will be deleted and then it comes to reference table (BETA_TRIGGER)  deletion, which is most probably throws null error [depends on the coding], because of master table had been deleted first and can’t find the reference row to delete based on where clause in the above example.

Now lets, move on to delete stored procedure which is demonstrated in the following,

CREATE OR REPLACE FUNCTION beta_delete_func() RETURNS TRIGGER AS $example_table1$
BEGIN
DELETE FROM beta_trigger_tbl WHERE beta_trigger_tbl.beta_tbl_pkid = old.pkid;
RETURN NEW;
END;
$example_table1$ LANGUAGE plpgsql;

As you can see there is not much different in the stored procedure structure except some basic commands.

The last portion is to check whether the triggers exist or not and if exist, the program should drop them before inserting the triggers, otherwise, PostgreSQL throws exception and causes the program (if any) to be disturbed.

For insert trigger, drop command is like this

DROP TRIGGER IF EXISTS beta_trigger_insert on beta_tbl;

And similarly for delete, the command is this

DROP TRIGGER IF EXISTS beta_trigger_delete on beta_tbl;