Database trigger is an event that can occur after or before a statement is executed or a row is modified / inserted / deleted. This can be used to perform any task before or after certain occurrence of an event in the database.

I was curious about this concept from a very long time and wanted to check it out.

I wanted to try an automation by creating a trigger function.

Trigger function in PostgreSQL is a kind of function to which special variables are passed — NEW, OLD etc. More on trigger functions — here

NEW — variable sent to trigger function when the trigger is INSERT / UPDATE. This variable will contain the new row to be inserted / updated.

OLD — variable sent to trigger function when the trigger is DELETE. This variable will contain the row to be deleted.

To try out trigger functions I created three tables posts, groups and user_posts. I wanted to try an insert automation, for example, on inserting a row into posts table, i wanted the DB to automatically insert rows into user_posts table. For this we need a trigger function like this :

CREATE FUNCTION update_user_post_association() 
returns TRIGGER language plpgsql AS $_$ 
DECLARE row user_groups%rowtype; 
begin 
  FOR row IN 
  SELECT   * 
  FROM   PUBLIC.user_groups 
  WHERE  group_id = new.group_id 
  loop 
  EXECUTE 'INSERT INTO public.user_posts VALUES ($1, $2, 1)' using row.user_id, 
    new.id; 
end 
loop; 
return new; 
end; 
$_$;

In the example we do the following steps:

• get all the users of the group
• loop on the users
• perform an insert using the available data

It is important to note that the trigger function here uses dynamic SQL statement which is slightly different from a normal SQL statement. When using variables in an SQL statement it is always good to use placeholders like $1, $2 and use the USING keyword to pass the variables.

Now that we have the trigger function, we need to tell the DB when to run this function. For this, we create a trigger

CREATE TRIGGER populate_users after 
INSERT 
OR 
UPDATE 
ON posts FOR each row 
EXECUTE PROCEDURE 
  update_user_post_association();

Now the DB executes the trigger function on every insert into the posts table.

Note : It is not a good idea to perform insert(s) in the trigger function because it may reduce the efficiency of the DB due to multiple inserts. When the DB is under heavy load, multiple inserts inside the trigger function might become slow and hence, the DB might start to queue operations.