SQL Tutorial

TRIGGERS IN SQL

🔄 SQL TRIGGERS

A trigger in SQL is a set of actions that are automatically executed or fired when a specific event occurs on a table or view. Triggers help enforce business rules, automatically audit changes, and maintain consistency.

💡 Tip: Triggers can be used for a variety of purposes, such as preventing invalid data entry, automating notifications, or updating other tables when data changes.

1️⃣ Creating a Trigger

The syntax for creating a trigger in SQL generally follows the format below. Triggers can be set to fire before or after an INSERT, UPDATE, or DELETE operation.

Example: Creating a trigger to log changes made to the employees table.

CREATE TRIGGER log_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO employee_audit (employee_id, action, timestamp)
  VALUES (OLD.employee_id, 'Updated', NOW());
END;

This trigger will insert a record into the employee_audit table whenever an employee's data is updated, logging the action and timestamp.

After an update is made to the employees table, the employee_audit table will automatically receive a new record.

| employee_id | action | timestamp           |
|-------------|--------|---------------------|
| 101         | Updated| 2025-05-15 14:35:20 |

2️⃣ Types of Triggers

SQL triggers can be classified into the following types based on when they are fired:

BEFORE Trigger: Executes before the specified operation (INSERT, UPDATE, DELETE) is performed.
AFTER Trigger: Executes after the specified operation is performed.
INSTEAD OF Trigger: Replaces the action of the operation (used for views).

3️⃣ BEFORE Trigger Example

A BEFORE trigger allows you to make changes to data before the action occurs. It can be used for validation or manipulation of data before insertion or updates.

Example: Creating a trigger to check if a new employee has a valid age before insertion.

CREATE TRIGGER check_employee_age
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  IF NEW.age < 18 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Age must be 18 or older.';
  END IF;
END;

This trigger checks if the new employee's age is less than 18 and raises an error if the condition is met.

4️⃣ INSTEAD OF Trigger Example

The INSTEAD OF trigger is typically used with views. It replaces the action for a view when an insert, update, or delete is performed.

Example: Creating an INSTEAD OF trigger to perform an insert on a view.

CREATE TRIGGER instead_of_employee_insert
INSTEAD OF INSERT ON employee_view
FOR EACH ROW
BEGIN
  INSERT INTO employees (name, age, department)
  VALUES (NEW.name, NEW.age, NEW.department);
END;

This trigger ensures that when an insert is made to the view employee_view, the corresponding data is inserted into the actual employees table.

5️⃣ Dropping a Trigger

If you no longer need a trigger, you can drop it using the DROP TRIGGER statement.

Example: Dropping a trigger.

DROP TRIGGER IF EXISTS log_employee_update;

This will drop the log_employee_update trigger if it exists.

Now you have a basic understanding of SQL triggers and how to use them! 🚀

🌟 Enjoyed Learning with Us?

Help others discover Technorank Learning by sharing your honest experience.
Your support inspires us to keep building!

Leave a Google Review

Topics