"When are Triggers in a PostgreSQL Database Necessary?"

PostgreSQL is one of the popular open-source relational database management systems. It offers a robust set of features to meet the diverse needs of developers and database administrators. One of these features are Triggers. Triggers are used to maintain the quality of a database by enforcing certain rules with response to some events that adds or changes data. In this blog we will explore the world of PostgreSQL triggers by exploring their functionality, best practices and use cases.

What are database triggers?

Triggers are a special kind of stored procedure that automatically invokes when some certain event such as an INSERT, UPDATE or DELETE operation occurs in the database server. Triggers can be predefined for a certain table or view. Triggers can be used to perform actions such as data validation. When a user tries to insert or change data , triggers are used to make sure that only valid datatypes are entered or the values are properly formatted. For example when an invalid value is being entered to a table, the trigger gets fired and the insertion is stopped and sometimes a message is displayed. Triggers are also used to enforce business rules. For example if the data entered is valid only when the dates are in a certain range. It can be used to prevent deletion of certain historic records or very important data or preventing an overdraft on bank accounts. Triggers can be used to derive additional data that is not available within a table or within the database . For example to add a timestamp value when someone logs in or populate a total payment column when interest value is changed.

One of the most important use of triggers is to enforce referential integrity. Referential integrity in any Relational Database Management Systems (RDBMS) is a fundamental concept that makes sure the consistency and accuracy of relationships between tables. Referential integrity is primarily ensured by primary and foreign keys. Triggers are used to warn and prevent when data is entered or altered with non-existent values. This ensures there is no data inconsistencies and preserves the quality of the database.

Triggers can be defined to execute before or after the triggering event and can be defined to execute for every row or once for every statement. Triggers are powerful feature of DataBase Management Systems that allow developers to define automatic actions based on database events. Let us have a look at triggers in PostgreSQL database.

Types of Triggers in PostgreSQL

In PostgreSQL Server, we can create the following types of triggers

• Event Triggers

• DML Triggers

PostgreSQL provides an event-based mechanism for reacting to changes in the database schema through the use of event triggers. Event triggers are similar in concept to DDL triggers and allow you to execute custom code in response to specific events. PostgreSQL event triggers are introduced in PostgreSQL 9.3 and later versions.

Here is an example of an event trigger a function ddl_event_trigger() gets executed when a DDL command ends.

-- Create an event trigger function
CREATE OR REPLACE FUNCTION ddl_event_trigger()
RETURNS event_trigger AS $$
BEGIN
    -- Your custom code here
    RAISE NOTICE 'DDL event occurred: %', current_event();
END;
$$ LANGUAGE plpgsql;

-- Create the event trigger 
CREATE EVENT TRIGGER ddl_event_trigger
ON ddl_command_end
EXECUTE FUNCTION ddl_event_trigger();

TYPES OF DML Triggers

PostgreSQL DML triggers are special functions or procedures that are automatically executed in response to Data Manipulation Language (DML) operations. Supported triggering events are AFTER, BEFORE and INSTEAD OF. They can be used for INSERT, UPDATE and DELETE events. Functions can be used to execute a complex SQL when the trigger gets invoked. PostgreSQL can execute this dynamically.

• BEFORE Trigger:A Before trigger executes before the triggering action, such as an INSERT, UPDATE or DELETE. This makes sure of the data validation and modification before the actual DML operation

• INSTEAD OF Trigger: An Instead of trigger is fired instead of the triggering action such as an INSERT, UPDATE or DELETE

• AFTER Trigger: An After trigger executes following the triggering action, such as an INSERT, UPDATE or DELETE. This typically is used for logging and auditing changes or updating additional tables or derived columns

SYNTAX

Basic syntax to create a trigger is as follows

CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
 -- Trigger logic codes..
];

Here event_name could be INSERT, DELETE, UPDATE and TRUNCATE database operation on the mentioned table table_name. You can optionally specify FOR EACH ROW after table name.

Here is an example of an UPDATE trigger that triggers when we try to update one or more columns on the table

CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
[
 -- Trigger logic codes..
];

Disadvantages of Triggers

Triggers are very helpful to keep the database quality it also has some disadvantages. They are

• Triggers may be difficult to troubleshoot as they execute automatically in the database. If there is some error then it is hard to find the logic of trigger because they are fired before or after updates/inserts happen.

• The triggers may increase the overhead of the database as they are executed every time any field is updated.

• They are not compiled.

• If we use the complex code in the trigger, it makes the application run slower.

Triggers are to be used wisely considering the above mentioned disadvantages. When working with triggers, always consider the potential for recursion (triggers calling other triggers) and ensure that your triggers are well-documented to facilitate maintenance and troubleshooting.