What, when and how of database triggers using PostgreSQL!

In this article, I will be explaining what is database triggers? (For the rest of this article I will be replacing the word “Database” with “DB”, not because it is more “Efficient” but because I am lazy enough to do it), when should one use DB triggers? and exactly how to write DB triggers with PostgreSQL?

WHAT is a DB trigger?

TRIGGER as the word is self-explanatory, triggers actually triggers something. I know it sounds dumb but it is what it is. In more technical terms DB triggers are the database objects which are associated with tables. A trigger is a stored procedure which gets executed before/after the occurrence of some event. In terms of DB, events can be insertion to the table, deletion from the table, modification of the records of the table. Apart from these mentioned events, there is also another special type of events called “INSTEAD OF”. This type of event although useful is a topic in itself and requires another separate article. “instead of” comes into the picture when you are dealing with views of multiple tables.

WHEN to use a DB trigger?

Triggers are generally used for the tasks that need to be executed no matter what, it needs to be triggered and database administrator/database designer does not have to worry about it. But it can be used for some NOT SO usual use cases as well.

Let me give you an example using the following problem. Let’s say you developed around 30 separate APIs for some XYZ project. After halfway through the project you came across the module which described itself as “User Activity”. You need to track when the users updated their profile pictures, updated the password of their accounts, when did they get notifications, what was the content of the notifications. In summary, you need to track when most of those APIs got served. What your first approach might be to solve this problem is by tracking each activity followed by updating each of those APIs. What a tedious and redundant work is that! What can be an alternative solution is to create a trigger for those database tables which are being interacted by those APIs underneath. Because in the end what APIs are really doing is manipulating/adding/deleting the data from the database.

How to implement DB trigger using PostgreSQL?

Trigger as a concept is similar in most of the database engines. But it’s implementation can be slightly different based on which engine you are using. We are going to implement the trigger using PostgreSQL. While most of the database engines have inline SQL statements that need to be executed, PostgreSQL triggers are different as they are associated with special kind of functions called ‘trigger functions’. These trigger functions get executed once the trigger is fired. For example, MySQL trigger definition looks something like this.

CREATE TRIGGER agecheck BEFORE INSERT ON people FOR EACH ROW IF NEW.age < 0 THEN SET NEW.age = 0; END IF;

While in PostgreSQL same trigger looks something like this.

CREATE TRIGGER agecheck BEFORE INSERT ON people
FOR EACH ROW EXECUTE PROCEDURE check_age_procedure();

Where check_age_procedure() function contains the actual logic. Let’s take an example to implement trigger functionality.

NOTE : This is also a benefit of PostgreSQL triggers, you can reuse code by using only one trigger function for multiple triggers.

Let’s say you have a project in which users can update their profile and can post blogs. You want to create a user activity mechanism for your project in which you need to log two simple activities such as when users update their profiles and when users post their blogs. Let’s create three tables. ‘user’, ‘blog’, ‘useractivity’.

NOTE : Before we write any code. BOLD: represents PostgreSQL keywords. TWO DASHES: represents user-defined comment

CREATE TABLE public."user"
(
    userid bigserial NOT NULL PRIMARY KEY,
    name character varying(200),
    email character varying(255),
    created timestamp without time zone,
    updated timestamp without time zone
);
CREATE TABLE public."blog"
(
    blogid bigserial NOT NULL PRIMARY KEY, 
    userid bigint NOT NULL,
    title character varying(255),
    blogbody text,
    created timestamp without time zone,
    updated timestamp without time zone,
    CONSTRAINT blog_userid_user FOREIGN KEY (userid)
      REFERENCES public."user" (userid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
);
CREATE TABLE public."useractivity"
(
    useractivityid bigserial NOT NULL PRIMARY KEY,
    message text,
    typeuseractivity character varying(255),
    contexttable character varying(200),
    contextid bigint,
    created timestamp without time zone,
    updated timestamp without time zone
);

Our objective here is to log user activities when users update their profile and when user post new blog. So we need to create two triggers, one for user profile modification and one for insertion in the blog table. Although we will create two different triggers we will be using only one trigger function in both of these triggers.

CREATE FUNCTION public.user_activity() RETURNS trigger AS $emp_stamp$
 DECLARE
  v_username CHARACTER VARYING;
 BEGIN
  -- Check for which table trigger was fired
  IF TG_ARGV[0] = 'user'
  THEN
   -- Check which kind of event which fired the trigger EX: (CREATE,UPDATE)
   IF TG_OP = 'UPDATE'
   THEN
    -- Check which user-table field got changed
    IF NEW.name != OLD.name  -- When name is changed
    THEN
     INSERT INTO public.useractivity 
     VALUES 
     (DEFAULT, NEW.name || ' updated their name from ' || OLD.name                                   ' || ' to ' || NEW.name,'userupdate', 'user', NEW.userid,      current_timestamp, current_timestamp);
    END IF;
    IF NEW.email != OLD.email -- When email is changed
    THEN
     INSERT INTO public.useractivity 
     VALUES (DEFAULT, NEW.name || ' updated their email from ' || OLD.email || ' to ' || NEW.email,
         'userupdate', 'user', NEW.userid, current_timestamp, current_timestamp);
    END IF;
   END IF;
  ELSIF TG_ARGV[0] = 'blog'
  THEN
   -- Get username from user's table
   v_username := (SELECT name FROM public.user WHERE userid = NEW.userid);
   -- Check which kind of event which fired the trigger EX: (CREATE,UPDATE)
   IF TG_OP = 'INSERT'  
   THEN
    INSERT INTO public.useractivity 
     VALUES (DEFAULT, v_username || ' created a blog post with title ' || NEW.title,
         'blogcreate', 'blog', NEW.blogid, current_timestamp, current_timestamp);
   END IF;
  END IF;
  RETURN NULL;
  END;
$emp_stamp$ LANGUAGE plpgsql;

Above given function handles two events for two tables ‘user’ and ‘blog’. It inserts a record in ‘useractivity’ table. Let’s talk about some special parameters which are being used in the trigger function.

TG_ARGV : As you can see trigger function does not accept any parameters, at least not in the definition. But you CAN pass parameters to trigger function and use it inside the function with TG_ARGV. TG_ARGV is an array of text. You can access it like a normal array through indexes. Ex. TG_ARGV[0].

TG_OP : This parameter has value based on which event fired the trigger. Ex. INSERT, UPDATE, DELETE, TRUNCATE.

After creating the trigger function, the next step is to create a trigger which actually executes the function.

CREATE TRIGGER user_update AFTER UPDATE ON public.user
    FOR EACH ROW EXECUTE PROCEDURE public.user_activity('user');
CREATE TRIGGER blog_insert AFTER INSERT ON public.blog
    FOR EACH ROW EXECUTE PROCEDURE public.user_activity('blog');

Let’s understand the syntax:

AFTER : This keyword determines when to fire the trigger. Whether BEFORE/AFTER.

UPDATE : This keyword determines the type of event which fires the trigger. Ex. UPDATE, INSERT, DELETE, TRUNCATE.

ON x : x here determines to which DB table this trigger should be attached to. In our example: ‘public.user’ and ‘public.blog’

This combination of event and table can be chained together using OR and as a result, one trigger can be used for different events and different tables. Example: AFTER UPDATE OR BEFORE INSERT ON public.xyz OR public.abc.

As you can see in the definition of triggers, I am passing a parameter (‘user’ or ‘blog’) to public.user_activity() in order to determine which table just got affected. We are accessing this parameter through TG_ARGV[0] as explained earlier.

The most important aspect of trigger lies in two objects NEW.something and OLD.something. For example, in the event of UPDATE when some record got changed OLD object holds previous values and NEW object holds the latest values.

If I have missed anything in explaining DB triggers, let me know in the comments. I would really appreciate the feedback.

As this was my first article ever on any blogging platform, I am open to suggestions to improve my technical and writing skills.