Article Overview

In this article we will cover the following topics:

• Database trigger and concept
• Types of triggers
• PostgreSQL trigger vs SQL Server trigger key differences
• PostgreSQL trigger operations overview
• Access Trigger via pgAdmin and psql

What is a Database Trigger?

A Trigger is a block of code that is automatically executed after some operation is performed on a database table or view, precisely after an Insert, Update, Delete operation. For example in a banking application, a trigger can be used to insert data in the history/audit table for all the original transactions taking place.

Some Key Points:

• A trigger can be invoked before or after an event. Triggers are very useful when a database is being used by multiple applications, and there is a great need to keep the database in sync at all times whenever certain data is modified.
• While triggers are really useful in automating data alterations and allowing easy auditing of data, there are some disadvantages of triggers, too. Since triggers are executed every time there is a modification of data, this can lead to system overhead.
• Another disadvantage of triggers is they are hard to track and understanding their logic can be difficult.

There are mainly two types of triggers: row and statement level triggers.

Row level trigger

A row level trigger is triggered every time a row in a table gets affected. For example, if a row level insert trigger is defined on a table where a single insert statement inserts 100 rows in the table the trigger is executed 100 times once for each row.

Statement level trigger

As the name implies a statement level trigger is executed only once per statement or per transaction. In other words whenever some operation is performed on a table irrespective of the number of rows being worked upon, the trigger is fired only once. Taking the above example, if a statement level insert trigger is defined on a table where a single insert statement inserts 100 rows in the table the trigger is executed one time.

PostgreSQL Triggers

Conceptually a PostgreSQL trigger is similar to that of a SQL Server trigger, but there are some key differences, which are listed below:

• PostgreSQL triggers supports truncate operations.
• PostgreSQL doesn't support triggers without a related trigger function

PostgreSQL Trigger supports the following DML operations:

• Create trigger -  Is used to create a trigger
• Drop trigger - Is used to drop a trigger
• Alter trigger - Is used to change the name of an existing trigger.
• Disable trigger–  Is used to disable a specific or all triggers associated with  table
• Enable trigger – Is used to enable a specific or all triggers associated with  table

PostgreSQL Trigger Basic Syntax:

CREATE TRIGGER trigger_name 
{BEFORE | AFTER} { event }
ON table_name
[FOR [EACH] { ROW | STATEMENT }]
EXECUTE PROCEDURE trigger_function

Let us study this syntax,

To start trigger_name is specified to create the trigger. This is followed by the timing of the trigger which can be either before or after depending on the operation to be performed on the target table. This is followed by the event which is one of the following -  insert, update, delete, truncate. Next the table_name is specified, followed by the type of the trigger which is  either row or statement. Finally, the associated trigger_function is specified.

PostgreSQL Trigger Function Basic Syntax:

CREATE FUNCTION trigger_function() 
   RETURNS TRIGGER 
   LANGUAGE PLPGSQL
AS $$
BEGIN
   -- trigger logic
END;
$$

A trigger function does not take any arguments and has a return value with the type trigger. Once a trigger function is defined it can be associated to one or more trigger events such as insert, update and delete.

Row Trigger Example

Scenario: There are two tables stocks & stock_audits, for every row of data inserted in table stocks the trigger stocks_trigger is executed thus inserting one row of data in the other table stocks_audit.

CREATE TRIGGER stocks_trigger 
AFTER INSERT ON public."Stocks"
FOR EACH ROW 
EXECUTE PROCEDURE stock_auditfunc();

CREATE OR REPLACE FUNCTION stock_auditfunc() RETURNS TRIGGER AS $my_table$
   BEGIN
      INSERT INTO stocks_audit(stock_id, entry_date) VALUES (new.ID, current_timestamp);
      RETURN NEW;
   END;
$my_table$ LANGUAGE plpgsql;

Tables before insert:

Table after insert and trigger execution:

INSERT INTO public."Stocks" 
VALUES (1,3000,'TCS');

Note: For bulk operation for example where 100 rows are affected at once, the row trigger is executed 100 times.

STATEMENT TRIGGER: The above trigger definition can be modified in the following way. This  would execute the trigger just once per operation irrespective of the number of rows affected.

CREATE TRIGGER stocks_trigger 
AFTER INSERT ON public."Stocks"
FOR EACH STATEMENT
EXECUTE PROCEDURE stock_auditfunc();

PostgreSQL Trigger Access Through PgAdmin and PSQL Terminal

PostgreSQL Triggers can be efficiently worked upon either via PgAdmin or via psql terminal as shown below:

Access Trigger(s) via PgAdmin:

Triggers are specific to tables and are enlisted against each table as shown below, Servers->User Server->Databases->User Database->Schemas->User Schema->Tables-> User Tables->Triggers

Access Trigger(s) via psql:

To list triggers against a specific table within a database in psql terminal, the following commands are available to use:

Step 1: Switch to required database

\c [database_name]

Step 2: List triggers

\dS [table_name]

Conclusion

In this article we learnt about the generic characteristics of a database trigger followed by PostgreSQL trigger and how they can be accessed via PgAdmin and psql terminal. We hope this article will help you get started on your PostgreSQL trigger journey.