How do you use the AFTER trigger to perform actions after an INSERT, UPDATE, or DELETE operation?
CarolTh
Posted by CarolTh
Last Updated: July 07, 2024
trigger database
In SQL, an AFTER trigger is a type of trigger that is automatically executed (or fired) after an INSERT, UPDATE, or DELETE operation has been performed on a table. You can use an AFTER trigger to perform various actions, such as logging changes, enforcing business rules, updating related tables, or notifying other systems. Here's how to create and use an AFTER trigger in SQL:
Syntax for Creating an AFTER Trigger
The syntax for creating an AFTER trigger varies slightly between different database management systems (DBMS). Below is a generalized format, followed by examples for specific systems.
General Syntax
CREATE TRIGGER trigger_name
AFTER INSERT | UPDATE | DELETE
ON table_name
FOR EACH ROW
BEGIN
    -- Trigger action goes here
END;
Example in MySQL
1. Trigger after INSERT
CREATE TRIGGER after_insert_example
AFTER INSERT
ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (employee_id, action, action_time)
    VALUES (NEW.id, 'INSERT', NOW());
END;
2. Trigger after UPDATE
CREATE TRIGGER after_update_example
AFTER UPDATE
ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (employee_id, action, action_time)
    VALUES (NEW.id, 'UPDATE', NOW());
END;
3. Trigger after DELETE
CREATE TRIGGER after_delete_example
AFTER DELETE
ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (employee_id, action, action_time)
    VALUES (OLD.id, 'DELETE', NOW());
END;
Example in PostgreSQL
In PostgreSQL, the syntax is quite similar, but you also have access to the OLD and NEW records.
1. Trigger after INSERT
CREATE OR REPLACE FUNCTION after_insert_function()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO employee_audit (employee_id, action, action_time)
    VALUES (NEW.id, 'INSERT', NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_insert_example
AFTER INSERT ON employees
FOR EACH ROW EXECUTE FUNCTION after_insert_function();
2. Trigger after UPDATE
CREATE OR REPLACE FUNCTION after_update_function()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO employee_audit (employee_id, action, action_time)
    VALUES (NEW.id, 'UPDATE', NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_update_example
AFTER UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION after_update_function();
3. Trigger after DELETE
CREATE OR REPLACE FUNCTION after_delete_function()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO employee_audit (employee_id, action, action_time)
    VALUES (OLD.id, 'DELETE', NOW());
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_delete_example
AFTER DELETE ON employees
FOR EACH ROW EXECUTE FUNCTION after_delete_function();
Key Points
1. NEW and OLD: In the triggers, NEW refers to the new row that is being inserted or updated, while OLD refers to the row that is being deleted or was previously present before an update. 2. FOR EACH ROW: This clause indicates that the trigger will fire for each row affected by the SQL statement. If this is omitted, the trigger would fire only once per statement. 3. Actions Inside Trigger: The actions you define inside the trigger can include SQL statements like INSERT, UPDATE, or DELETE into other tables, logging operations, or even sending notifications. 4. Performance Impact: Triggers can impact performance, especially if they perform heavy operations, so use them judiciously. 5. Debugging: Debugging triggers can be challenging since they operate behind the scenes, so ensure your logic is well-tested. Using AFTER triggers effectively allows you to maintain data integrity and automate various tasks in your database system.
Default Image