Triggers

Triggers in databases are special types of stored procedures that are automatically executed or fired in response to certain events or actions occurring in the database.
These events can include insertions, updates, or data deletions in tables and changes to database schema objects like tables or views.

Note

Triggers cannot be invoked explicitly by users. Instead, they are automatically executed in response to the operations they are associated with.

DML triggers

A DML trigger, also known as a Data Manipulation Language trigger, is a database object that automatically executes a specified action when a certain event occurs on a table or view.
DML triggers are often used to enforce business rules, maintain data integrity, or perform auditing tasks.

DML trigger types

• After Triggers: These triggers fire after the triggering action (insert, update, delete) has been executed on the table;
• Before Triggers: These triggers fire before the triggering action. They allow you to intercept the original action and perform custom logic before deciding whether or not to proceed with the original action.

After trigger example

We've previously explored an example of using a transaction to add values to the BankAccounts table and the UserLogs table as a single logical unit in the first section of this course.
Now, instead of using a transaction, we can achieve this by creating an AFTER trigger on the INSERT operation within the BankAccounts table.
As a result, we will only be able to insert new accounts, and the corresponding log value will be automatically added by the trigger.

Note

In PostgreSQL triggers, NEW is a special record variable representing the new row inserted into or updated in the table. It allows you to access the values of columns in the new row within the trigger function.
If we work with deleted rows, we have to use OLD record variable instead of NEW.

Before trigger example

Assume we want to prevent adding values with a negative account balance. We can create a BEFORE trigger to achieve this. This trigger will check the balance before the operation, and if it is negative, the operation will not be performed.

Trigger creation pattern

We can observe the typical pattern for creating triggers: initially, we define a function to execute specific logic before or after an operation. Subsequently, we associate this function as a trigger to a designated table and action.

Please note that in the statement:

we have the flexibility to utilize UPDATE or DELETE, in place of INSERT operation.
But pay attention that we can't create SELECT triggers as the SELECT statement doesn't modify any rows in the table!