Learn Triggers | Triggers, Window Functions and DCL

Swipe to show menu

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.

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.


              12345678910111213141516
            
-- 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();

Query Description

Create an after insert trigger for user_logs:
- Create a function named afterinserttriggerfunction().
- The function is defined to return a trigger.
- The function's body begins with a BEGIN block.
- Inside the BEGIN block, an action is performed, which is inserting a record into userlogs table.
- The inserted record contains values from the NEW row of the triggering event.
- The values inserted include accountnumber and a concatenated string mentioning the action.
- The function returns the NEW row.
- The function is written in plpgsql language.
Attach the trigger to the bankaccounts table:
- A trigger named afterinserttrigger is created.
- The trigger is set to execute after an INSERT operation on the bankaccounts table.
- The trigger is set to execute the afterinserttriggerfunction function for each affected row.

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.


              12345678910111213141516171819202122232425
            
-- 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();

Query Description

Create the trigger function:
- Create or replace a function named before_insert_userlogs().
- The function is defined to return a trigger.
- The function's body begins with a BEGIN block.
- Inside the BEGIN block, a condition checks if the account balance is less than 0.
- If the balance is less than 0, a notice is raised and no action is logged. The function returns NULL to prevent the insertion.
- If the balance is greater than or equal to 0, a notice is raised indicating the action is logged.
- The function then inserts a record into the userlogs table, containing values from the NEW row.
- The inserted values include accountnumber and a concatenated string indicating the action.
- The function returns NEW to allow the insertion to proceed.
- The function is written in plpgsql language.
Create the trigger:
- A trigger named before_insert_userlogs_trigger is created.
- The trigger is set to execute before an INSERT operation on the bankaccounts table.
- The trigger is set to execute the before_insert_userlogs() function for each affected row.

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:

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!

Everything was clear?

Thanks for your feedback!

Section 3. Chapter 1

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Section 3. Chapter 1