Database Triggers

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 - 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 connections.