PostgreSQL Trigger Examples On Before And After

PostgreSQL triggers are mostly useful functionalities for executing queries automatically before or after a query runs. A PostgreSQL trigger is a function that is automatically executed in response to certain performing in the database, such as a update in data or the insertion of a new record. So, Let’s dive into it and its some examples.

What is a PostgreSQL Trigger?

A trigger is a function that is executed automatically when certain events occur in a PostgreSQL database. These events can include the insertion, update or deletion of records in a table or the creation or deletion of a table. Triggers can be defined to execute before or after the event and they can be used to enforce constraints, validate data or perform other operations.

The syntax for a PostgreSQL trigger function is as follows:

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE | TRUNCATE}
ON table_name
[REFERENCING {OLD | NEW} TABLE AS alias]
[FOR EACH {ROW | STATEMENT}]
[WHEN (condition)]
EXECUTE FUNCTION function_name(arguments);

The elements of this syntax are as follows:

• trigger_name: The name of the trigger you are creating.
• {BEFORE | AFTER}: Indicates whether the trigger should be executed before or after the event.
• {INSERT | UPDATE | DELETE | TRUNCATE}: Specifies the event that will trigger the function.
• table_name: The name of the table on which the trigger is being created.
• [REFERENCING {OLD | NEW} TABLE AS alias]: Optional clause that allows you to refer to the old or new values of the row being modified in the trigger function.
• [FOR EACH {ROW | STATEMENT}]: Specifies whether the trigger function should be executed once per row or once per statement.
• [WHEN (condition)]: Optional clause that allows you to specify a condition that must be met for the trigger to execute.
• EXECUTE FUNCTION function_name(arguments): The name of the function that will be executed when the trigger is fired, along with any arguments that the function requires.

PostgreSQL Trigger Examples

PostgreSQL Trigger After Insert

Let’s consider an example where we have a table named employees that contains information about employees in a company. We want to create a trigger that automatically inserts a record into a separate logs table whenever a new employee is added to the employees table.

Here is the PostgreSQL query for creating this trigger:

CREATE TRIGGER employee_added
AFTER INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION log_new_employee();

In this query, we have specified that the trigger should be executed after an INSERT operation on the employees table. We have also specified that the trigger should be executed once per row (FOR EACH ROW) and that the function log_new_employee() should be executed when the trigger is fired.

Trigger Function On After Insert

Trigger functions are user-defined functions that are executed automatically when a trigger is fired. These functions can contain any valid SQL or PL/pgSQL code and they can perform a wide range of operations, including inserting, updating or deleting records in tables.

When a trigger function is executed, the function has access to the OLD and NEW records in the table being modified. The OLD record represents the record before the modification, while the NEW record represents the record after the modification. The trigger function can use these records to perform operations based on the changes that were made.

Let’s back to an example of a trigger function that is executed when a new employee is added to the employees table. The function should insert a record into a separate logs table that contains the name of the new employee and the date that they were added.

CREATE OR REPLACE FUNCTION log_new_employee()
RETURNS TRIGGER
AS $$
BEGIN
    INSERT INTO logs (employee_name, created_at)
    VALUES (NEW.name, NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

In this query snippet, The function inserts a new record into the logs table using the NEW record from the employees table to get the name of the new employee. We also include the current date and time using the NOW() function. Finally, we return the NEW record to allow the insertion to proceed.

PostgreSQL Trigger Before Insert

Mostly before insert triggers are used for data validation. In this example, let’s assume again you have a employees table and you want to check if records such as age and salary set properly whenever an insertion action attempted to the employees table.

Here is the query for this purpose:

CREATE TRIGGER before_insert_employee
BEFORE INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION validate_employee_data();

In this query, we have specified that the trigger should be run after an AFTER operation on the employees table. We have also specified that the trigger should be executed once per row (FOR EACH ROW) and that the function validate_employee_data() should be executed when the trigger is fired.

Trigger Function On Before Insert

In this query, validate_employee_data() will be executed whenever a new employee is attempted to be added to the employees table. It will validate records before inserting operation, If any of the validation conditions fail, an exception is raised, preventing the insertion of invalid employee data.

CREATE OR REPLACE FUNCTION validate_employee_data()
RETURNS TRIGGER AS $$
BEGIN
    -- Check if age is within valid range
    IF NEW.age < 18 OR NEW.age > 65 THEN
        RAISE EXCEPTION 'Employee age must be between 18 and 65.';
    END IF;

    -- Check if department is not empty
    IF NEW.department IS NULL OR NEW.department = '' THEN
        RAISE EXCEPTION 'Employee department cannot be empty.';
    END IF;

    -- Check if salary is positive
    IF NEW.salary <= 0 THEN
        RAISE EXCEPTION 'Employee salary must be a positive value.';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

For instance, if you try to insert an employee with an age less than 18, an empty department, or a negative salary, the trigger will raise an exception and the insertion operation will be halted, ensuring that only valid employee data is inserted into the table.

Conclusion

PostgreSQL Trigger is a powerful feature that allows queries to be executed automatically before or after events such as insert, update and delete in your database. You can automate many database operations by defining trigger functions that execute SQL or PL/pgSQL code.

Thank you for reading.