Tutorial: How to Create Database Triggers

Namaste,

Myself Aditya, working as Data Scientist in a fintech startup. I have been postponing this article since long time. This tutorial is about creating database trigger.

Level: Beginner
Time: 5 minute read
Activity: 10 minutes (Provided PostgreSQL is installed)

databasetrigger

Being a Data Scientist my day job not only includes analyzing data, creating models, visualizing & explaining the results to stake holders but also to move my models to production through which I get immense satisfaction because that is where I make some real impact on business. While implementing one such model I faced an interesting problem.

First let us analyze the problem

There will be tables which will be used as loggers as well as to pull single unique records with some flag. Example for such a table is as follows:

id name company exp location is_active
1 aditya impact 3 delhi n
2 aditya cybermotion 10 hyderabad n
3 aditya earlysalary 15 pune y
4 naveen infosys 3 hyderabad n
5 naveen inooga 28 hyderabad y

This can be queried as follows:

query1:
select * from emp_table where name = 'naveen';

or

query2:
select * from emp_table where is_active = 'y'; 

The output looks like:

for query 1: All records of naveen are displayed.This is his employment history.

id name company exp location is_active
4 naveen infosys 3 hyderabad n
5 naveen inooga 28 hyderabad y

for query 2: It gives us information about currently in which company they are working.

name company exp location is_active
aditya earlysalary 15 pune y
naveen inooga 28 hyderabad y

This gives us an idea about how single table can be used both as logger (query 1 output can be called as logger because we have all information about naveen’s employment history) and also the same table can be used to retrieve unique records of each employee using column is_active. This will be used to know where they are currently working or in which location or what is their current experience.

Everything is fine till now, now Naveen had got a new job in facebook we have to insert a new record like this.

id name company exp location is_active
4 naveen infosys 3 hyderabad n
5 naveen inooga 28 hyderabad n
6 naveen facebook 35 united states y

The task is, before inserting we have to make all his previous records to ‘n’ and make the new insertion record to ‘y’

We can do this in two possible ways

  1. One way is to query the same table whether there is an old record of naveen and update is_active column to ‘n’ and inserting new row with default ‘y’. This is the traditional way.
  2. Second way is to do it with database trigger instead of another select and update. Having database trigger will save your additional code and its completely automatic whenever insertion happens the trigger will set all previous records of naveen to ‘n’ and the latest record to ‘y’.

Leaving aside the debate of how good or bad are database triggers for maintenance and security, let us focus on learning how to create a trigger for is_active column on such a table.

Tutorial starts from here…..

Step – 1 : Creating a table

I am using PostgreSQL for this tutorial. If you are not a user of PostgreSQL this is the right time to start, please follow this link to install PostgreSQL on your machine : PostgreSQL installation link

Let’s give life to our above example. Create a table in PostgreSQL using following code.

create table employee (id serial,
   name character varying,
   company character varying,
   exp integer,
   location character varying,
   is_active char(1) default 'y');

Go ahead and insert two rows in the employee table using the following code.

insert into employee (name,company,location) values ('aditya','impact','delhi'),
                                               ('naveen','infosys','hyderabad');

This will insert two rows in your table which can be checked using

select * from employee;

Step – 2 : Creating trigger

In this step there will be two parts

  1. Creating a trigger function.
  2. Creating a trigger.

First let us create a trigger function using the following code.

CREATE OR REPLACE FUNCTION is_active_employee()
RETURNS trigger AS
$BODY$
DECLARE
name_temp character varying;
BEGIN
IF (TG_OP = 'INSERT') THEN
name_temp = NEW."name";
END IF;
UPDATE employee SET is_active = 'n' where name = name_temp;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE

$BODY$ – Everything you write should be within this $BODY$ clause, don’t forget to end it with $BODY$ else it will throw an error.
TG_OP – Translates to Trigger Operation (In our case it is INSERT other possibilities are like UPDATE).
LANGUAGE plpgsql VOLATILE – It’s the Language name, PostgreSQL Procedural Language.

Lets understand what we have created, created is_active_employee function which will return trigger when there is insertion on employee table it will take the new insertion’s ‘name’ field and store it in a temporary variable called ‘name_temp’ variable and then update all the records is_active column to ‘n’ with the same ‘name’.

(You may ask me why we are using ‘name’ column which may collide with two persons having common name. Definitely true, we should not use fields like name to update. I am using it here for simplicity and as example. Generally we should use unique id for example employee id.)

After executing the above code go and check in the list where you find tables under your working schema of PostgreSQL, you should find your trigger function is_active_employee under trigger functions like this.

1

It’s time to create trigger on your table with the following code.

CREATE TRIGGER emp_trigger
BEFORE INSERT
ON employee
FOR EACH ROW
EXECUTE PROCEDURE is_active_employee();

If you observe the syntax and read, it says it is creating a trigger called emp_trigger to perform some action before insertion on employee table which is applicable for each row which means the procedure is_active_employee function will be called and get executed on each row of insertion.

Step – 3 : Checking the trigger functionality

For this we just need to insert a new row of ‘aditya’. Lets do that.

insert into employee (name,company,location) values 
('aditya','cybermotion','hyderabad');

After inserting lets check whether our trigger had made changes to is_active column or not,

select * from employee order by id;

2

So this is how we create database triggers, hope you enjoyed this tutorial and really learnt to create database triggers, feel free to experiment -> fail -> learn & succeed. Do share your experiences with database triggers in the comments section below.

~Aditya

Advertisements