Trigger in Postgres

The trigger is a function that invokes automatically when an event associated with a table occurs like an update, delete, or insert.

Triggers can be defined to execute either at the statement level or the row level.

1. Statement-level triggers: Statement-level triggers are executed once for each triggering SQL statement, regardless of the number of rows affected by the statement. Statement-level triggers are useful when the action to be taken by the trigger is independent of the number of rows being affected. Statement-level triggers are defined using the FOR EACH STATEMENT clause.

2. Row-level triggers: Row-level triggers are executed once for each row affected by the triggering SQL statement. Row-level triggers are useful when the action to be taken by the trigger depends on the data in the affected rows. Row-level triggers are defined using the FOR EACH ROW clause.

Types of Trigger

1. Before Trigger Example: Suppose we have a table called "employees" with columns "id", "name", and "salary", and we want to make sure that the salary of any employee being updated is not less than 1000. We can create a before trigger to perform this validation:

CREATE OR REPLACE FUNCTION validate_salary()
RETURNS trigger AS
$BODY$
BEGIN
    IF NEW.salary < 1000 THEN
        RAISE EXCEPTION 'Salary cannot be less than 1000';
    END IF;
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER check_salary
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION validate_salary();

This trigger function will be executed before any update on the "employees" table, and will check if the new salary value is less than 1000. If so, it will raise an exception and prevent the update from occurring.

1. After Trigger Example: Suppose we have a table called "orders" with columns "id", "customer_id", "total", and "date", and we want to log any changes made to this table. We can create an after trigger to perform this logging:

CREATE OR REPLACE FUNCTION log_changes()
RETURNS trigger AS
$BODY$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO order_logs (order_id, event, date)
        VALUES (NEW.id, 'inserted', now());
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO order_logs (order_id, event, date)
        VALUES (NEW.id, 'updated', now());
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO order_logs (order_id, event, date)
        VALUES (OLD.id, 'deleted', now());
    END IF;
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER orders_log
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION log_changes();

This trigger function will be executed after any insert, update, or delete on the "orders" table, and will log the event (inserted, updated, or deleted) and the current date/time into an "order_logs" table.

1. Instead of Trigger Example: Suppose we have a view called "customers_orders" that combines the "customers" and "orders" tables to show the customer name, order total, and order date. We want to make sure that any updates to this view only affect the "orders" table, not the "customers" table. We can create an instead of trigger to handle this:

CREATE OR REPLACE FUNCTION update_orders()
RETURNS trigger AS
$BODY$
BEGIN
    UPDATE orders
    SET total = NEW.total, date = NEW.date
    WHERE id = NEW.id;
    RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER customers_orders_update
INSTEAD OF UPDATE ON customers_orders
FOR EACH ROW
EXECUTE FUNCTION update_orders();

This trigger function will be executed instead of any update on the "customers_orders" view, and will update the "orders" table based on the new values provided. It will return NULL to indicate that the view should not be updated directly. This way, any updates to the "customers_orders" view will only affect the "orders" table, not the "customers" table.
percona