Impara Triggers

Ottimizzazione SQL e Funzionalità delle Query

Scorri per mostrare il menu

Triggers are special database objects that automatically execute a set of SQL statements in response to certain events on a table, such as inserts, updates, or deletes. Triggers allow you to automate tasks that need to happen whenever data changes, helping to enforce business rules or maintain audit trails without requiring manual intervention.

There are two main types of triggers you will encounter: BEFORE and AFTER triggers. A BEFORE trigger runs before the triggering event occurs, which allows you to validate or modify data before it is written to the table. An AFTER trigger runs after the event, making it ideal for actions that depend on the data already being changed or committed, such as logging the change or updating related tables.

Imagine you have an employees table that stores salary information, and a salary_changes table that should record every time an employee's salary is updated. You can use an AFTER UPDATE trigger on the employees table to automatically insert a record into salary_changes whenever an employee's salary changes. This approach ensures that all salary changes are tracked consistently and reliably, without relying on application code or user actions.


              123456789101112131415161718192021222324
            
-- Drop the trigger and function if they exist to clean up before creating new ones
DROP TRIGGER IF EXISTS after_salary_update ON employees;
DROP FUNCTION IF EXISTS log_salary_change();

CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO salary_changes (employee_id, old_salary, new_salary, change_date)
    VALUES (NEW.employee_id, OLD.salary, NEW.salary, CURRENT_DATE);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_salary_update
AFTER UPDATE OF salary ON employees
FOR EACH ROW
WHEN (OLD.salary IS DISTINCT FROM NEW.salary)
EXECUTE FUNCTION log_salary_change();

-- Example update to trigger the salary change logging
UPDATE employees SET salary = salary + 1000 WHERE employee_id = 1;

-- View the logged salary changes
SELECT * FROM salary_changes WHERE employee_id = 1;

Let’s break down how this trigger works and what effect it has on your tables. The log_salary_change function is defined as a trigger function: whenever it is called, it will insert a new row into the salary_changes table, recording the employee’s ID, their old salary, their new salary, and the date of the change. The AFTER UPDATE OF salary ON employees part means the trigger will fire only after the salary column in the employees table is updated. The WHEN clause ensures the trigger only runs if the salary actually changes.

When you update an employee’s salary in the employees table, the trigger automatically adds a record to salary_changes with all relevant details. This guarantees a reliable audit trail for salary adjustments, and you do not need to remember to log changes manually. Triggers like this are powerful tools for automating business logic and maintaining data integrity across related tables.

Tutto è chiaro?

Grazie per i tuoi commenti!

Sezione 1. Capitolo 15

Chieda ad AI

Chieda pure quello che desidera o provi una delle domande suggerite per iniziare la nostra conversazione

Sezione 1. Capitolo 15

Triggers

1. What is the purpose of a trigger?

2. When would you use an AFTER trigger?

3. Can triggers modify data in other tables?