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.

sql
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Create an after-insert trigger for UserLogs
CREATE FUNCTION after_insert_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    -- Perform some action, like logging the insert
    INSERT INTO UserLogs (account_number, action)
    VALUES (NEW.account_number, CONCAT('New record inserted for ', NEW.account_holder));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Attach the trigger to the BankAccounts table
CREATE TRIGGER after_insert_trigger
AFTER INSERT ON BankAccounts
FOR EACH ROW
EXECUTE PROCEDURE after_insert_trigger_function();

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.

sql
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- Create the trigger function
CREATE FUNCTION before_insert_trigger_function() RETURNS TRIGGER AS $$
BEGIN
    -- Check if the account balance is less than 0
    IF (NEW.balance < 0) THEN
        -- If balance is less than 0, do nothing and return
        RAISE NOTICE 'Balance is less than 0. Action not logged.';
    ELSE
        -- If balance is greater than or equal to 0, log the action
        RAISE NOTICE 'Logging insert into UserLogs';
        -- Inserting into the UserLogs table
        INSERT INTO UserLogs (account_number, action)
        VALUES (NEW.account_number, CONCAT('Account created for ', NEW.account_holder));
    END IF;

    -- Indicate that the trigger function has completed successfully
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Create the trigger
CREATE TRIGGER before_insert_trigger
BEFORE INSERT ON BankAccounts
FOR EACH ROW
EXECUTE PROCEDURE before_insert_trigger_function();

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:

sql
9
1
2
3
4
CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
EXECUTE PROCEDURE proc_name;

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!