Home » Courses » SQL Crash Course

SQL Triggers – Automating Actions Based on Database Events

Lesson 15: SQL Triggers – Automating Actions Based on Database Events

Estimated Read Time: 20-25 minutes

Objective: In this lesson, we will learn about SQL Triggers, which allow you to automate database actions in response to specific events such as INSERT, UPDATE, or DELETE. Triggers are a powerful way to ensure data integrity and automate business logic at the database level.

1. What is a Trigger in SQL?

A trigger is a stored procedure in SQL that is automatically executed (or “triggered”) when a certain event occurs on a specified table or view. Triggers can be used to enforce business rules, automatically update related data, or maintain data consistency when certain actions take place.

For example, you can create a trigger that updates a record in one table whenever a record in another table is updated. Triggers can be set to respond to the following events:

INSERT: Triggered when a new row is inserted into the table.
UPDATE: Triggered when a row is updated in the table.
DELETE: Triggered when a row is deleted from the table.

2. Syntax for Creating a Trigger

Triggers are created using the CREATE TRIGGER statement, followed by the event type, the table to apply the trigger to, and the action to be performed.

The basic syntax for creating a trigger is as follows:

CREATE TRIGGER trigger_name
AFTER INSERT | UPDATE | DELETE
ON table_name
FOR EACH ROW
BEGIN
   -- Triggered action goes here
END;

Here’s a breakdown of the parts of the syntax:

trigger_name: The name of the trigger.
AFTER/BEFORE: Specifies whether the trigger will be executed before or after the event.
INSERT | UPDATE | DELETE: Specifies the type of event that will trigger the action.
table_name: The table to which the trigger applies.
FOR EACH ROW: Indicates that the trigger is fired for each row affected by the event.
BEGIN … END: The block of code to execute when the trigger is fired.

3. Example 1: Creating a Trigger for an Insert Event

Let’s say you have an employees table, and you want to automatically update the audit_log table whenever a new employee is added.

Here’s how you can create a trigger to log this event:

CREATE TRIGGER log_employee_insert
AFTER INSERT
ON employees
FOR EACH ROW
BEGIN
   INSERT INTO audit_log (action, table_name, timestamp)
   VALUES ('INSERT', 'employees', NOW());
END;

This trigger is set to execute after a new row is inserted into the employees table. It inserts a record into the audit_log table, logging the action (INSERT), the table name, and the timestamp of the action.

4. Example 2: Creating a Trigger for an Update Event

Now, let’s create a trigger that updates a record in the audit_log table when a salary update occurs in the employees table:

CREATE TRIGGER log_employee_update
AFTER UPDATE
ON employees
FOR EACH ROW
BEGIN
   IF OLD.salary != NEW.salary THEN
      INSERT INTO audit_log (action, table_name, timestamp)
      VALUES ('UPDATE', 'employees', NOW());
   END IF;
END;

This trigger checks whether the salary field has changed (using the OLD and NEW values) and logs an update action if the salary is modified.

5. Example 3: Creating a Trigger for a Delete Event

Let’s now create a trigger that logs when an employee is deleted from the employees table:

CREATE TRIGGER log_employee_delete
AFTER DELETE
ON employees
FOR EACH ROW
BEGIN
   INSERT INTO audit_log (action, table_name, timestamp)
   VALUES ('DELETE', 'employees', NOW());
END;

This trigger automatically records the delete action in the audit_log table whenever an employee is removed from the employees table.

6. Trigger Timing: BEFORE vs AFTER

The timing of a trigger determines when it is executed in relation to the event (INSERT, UPDATE, DELETE).

BEFORE: The trigger is executed before the actual operation (INSERT, UPDATE, DELETE) takes place.
AFTER: The trigger is executed after the operation is completed.

For example, if you want to validate data before it’s inserted into the database, you would use a BEFORE INSERT trigger. If you want to perform an action after data has been inserted (like logging the event), you would use an AFTER INSERT trigger.

7. Example 4: BEFORE Insert Trigger for Data Validation

Let’s create a trigger that checks if an employee’s salary is greater than a minimum value before allowing the insertion:

CREATE TRIGGER validate_salary_before_insert
BEFORE INSERT
ON employees
FOR EACH ROW
BEGIN
   IF NEW.salary < 30000 THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary must be greater than 30000';
   END IF;
END;

This trigger checks the NEW.salary value before inserting a record and raises an error if the salary is below 30,000, preventing the insert.

8. Dropping and Altering Triggers

To remove a trigger from the database, use the DROP TRIGGER statement:

DROP TRIGGER trigger_name;

To alter an existing trigger, you would need to drop and recreate it, as SQL does not support direct modifications to triggers.

9. Performance Considerations

Triggers are powerful, but they can also impact database performance. Since they are executed automatically, excessive or complex triggers can slow down data manipulation operations. It’s important to use triggers judiciously and ensure they are optimized to prevent performance issues.

10. Conclusion

In this lesson, we learned about SQL triggers and their role in automating actions based on specific database events. Triggers help enforce business rules, maintain data integrity, and automate repetitive tasks at the database level. You can create triggers for INSERT, UPDATE, and DELETE events, and use them to handle actions like logging, data validation, or cascading updates.

We covered:

What triggers are and how they work in SQL.
How to create, modify, and drop triggers.
Examples of common triggers for inserting, updating, and deleting data.
Trigger timing: BEFORE vs AFTER triggers.

In the next lesson, we will dive into SQL Views to simplify complex q

SQL Crash Course