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 $_$
    row user_groups%rowtype;
    FOR row in SELECT * from public.user_groups WHERE group_id = NEW.group_id
        EXECUTE 'INSERT INTO public.user_posts VALUES ($1, $2, 1)' USING row.user_id,;

In the example we do the following steps:

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.


Now read this

Markers with D3

Every time I look at the examples page of D3, I’m simply go… @mbostock has transformed how visualizations are created for web. Today I learnt how to use svg markers with D3. I was using force layout to analyze graphs, just like this... Continue →