How do you use the AFTER trigger to perform actions after an INSERT, UPDATE, or DELETE operation?
Posted by CarolTh
Last Updated: July 07, 2024
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
  1. CREATE TRIGGER trigger_name
  2. AFTER INSERT | UPDATE | DELETE
  3. ON table_name
  4. FOR EACH ROW
  5. BEGIN
  6. -- Trigger action goes here
  7. END;
Example in MySQL
1. Trigger after INSERT
  1. CREATE TRIGGER after_insert_example
  2. AFTER INSERT
  3. ON employees
  4. FOR EACH ROW
  5. BEGIN
  6. INSERT INTO employee_audit (employee_id, action, action_time)
  7. VALUES (NEW.id, 'INSERT', NOW());
  8. END;
2. Trigger after UPDATE
  1. CREATE TRIGGER after_update_example
  2. AFTER UPDATE
  3. ON employees
  4. FOR EACH ROW
  5. BEGIN
  6. INSERT INTO employee_audit (employee_id, action, action_time)
  7. VALUES (NEW.id, 'UPDATE', NOW());
  8. END;
3. Trigger after DELETE
  1. CREATE TRIGGER after_delete_example
  2. AFTER DELETE
  3. ON employees
  4. FOR EACH ROW
  5. BEGIN
  6. INSERT INTO employee_audit (employee_id, action, action_time)
  7. VALUES (OLD.id, 'DELETE', NOW());
  8. 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
  1. CREATE OR REPLACE FUNCTION after_insert_function()
  2. RETURNS TRIGGER AS $$
  3. BEGIN
  4. INSERT INTO employee_audit (employee_id, action, action_time)
  5. VALUES (NEW.id, 'INSERT', NOW());
  6. RETURN NEW;
  7. END;
  8. $$ LANGUAGE plpgsql;
  9.  
  10. CREATE TRIGGER after_insert_example
  11. AFTER INSERT ON employees
  12. FOR EACH ROW EXECUTE FUNCTION after_insert_function();
2. Trigger after UPDATE
  1. CREATE OR REPLACE FUNCTION after_update_function()
  2. RETURNS TRIGGER AS $$
  3. BEGIN
  4. INSERT INTO employee_audit (employee_id, action, action_time)
  5. VALUES (NEW.id, 'UPDATE', NOW());
  6. RETURN NEW;
  7. END;
  8. $$ LANGUAGE plpgsql;
  9.  
  10. CREATE TRIGGER after_update_example
  11. AFTER UPDATE ON employees
  12. FOR EACH ROW EXECUTE FUNCTION after_update_function();
3. Trigger after DELETE
  1. CREATE OR REPLACE FUNCTION after_delete_function()
  2. RETURNS TRIGGER AS $$
  3. BEGIN
  4. INSERT INTO employee_audit (employee_id, action, action_time)
  5. VALUES (OLD.id, 'DELETE', NOW());
  6. RETURN OLD;
  7. END;
  8. $$ LANGUAGE plpgsql;
  9.  
  10. CREATE TRIGGER after_delete_example
  11. AFTER DELETE ON employees
  12. 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.