Introduction to Triggers in SQL

Triggers in SQL are special types of stored procedures that automatically execute or "fire" in response to certain events on a particular table or view. They are often used to enforce business rules, perform auditing tasks, or automatically update related data when changes occur in the database. In this article, we will explore what triggers are, how they work, and provide examples of their usage.

What is a Trigger?

A trigger is a set of SQL statements that are automatically executed in response to certain events. These events can include actions like inserting, updating, or deleting records in a table. A trigger is associated with a specific table or view and is defined to execute when a particular type of event occurs.

Triggers are commonly used for:

  • Enforcing data integrity or business rules
  • Maintaining audit logs
  • Automatically updating related data in other tables

Types of Triggers

There are several types of triggers in SQL based on the type of event they respond to:

  • INSERT Trigger: Fires when a new row is inserted into the table.
  • UPDATE Trigger: Fires when an existing row in the table is updated.
  • DELETE Trigger: Fires when a row is deleted from the table.

Triggers can also be classified based on when they execute:

  • BEFORE Trigger: Executes before the triggering event (INSERT, UPDATE, or DELETE) is applied to the table.
  • AFTER Trigger: Executes after the triggering event has been completed.
  • INSTEAD OF Trigger: Executes instead of the triggering event.

Creating a Trigger

The syntax for creating a trigger in SQL is:

        CREATE TRIGGER trigger_name
        { BEFORE | AFTER | INSTEAD OF } { INSERT | UPDATE | DELETE }
        ON table_name
        FOR EACH ROW
        BEGIN
            -- SQL statements to execute when the trigger fires
        END;

Let’s now look at some examples to better understand how triggers work.

Example 1: Creating an INSERT Trigger

In this example, we will create a trigger that fires when a new row is inserted into the Employees table. The trigger will automatically insert a record into the AuditLogs table to keep track of the insertion.

        CREATE TRIGGER LogEmployeeInsertion
        AFTER INSERT
        ON Employees
        FOR EACH ROW
        BEGIN
            INSERT INTO AuditLogs (Action, TableName, Timestamp)
            VALUES ('INSERT', 'Employees', NOW());
        END;

In this example, every time a new employee is added to the Employees table, a new entry is made in the AuditLogs table, noting the action (INSERT), the table name, and the timestamp of the event.

Example 2: Creating an UPDATE Trigger

In this example, we will create a trigger that fires when an employee’s salary is updated. The trigger will insert a record into an AuditLogs table to track the changes made to an employee’s salary.

        CREATE TRIGGER LogSalaryUpdate
        AFTER UPDATE
        ON Employees
        FOR EACH ROW
        BEGIN
            IF OLD.Salary != NEW.Salary THEN
                INSERT INTO AuditLogs (Action, TableName, Timestamp, Details)
                VALUES ('UPDATE', 'Employees', NOW(), 'Salary changed from ' 
                || OLD.Salary || ' to ' || NEW.Salary);
            END IF;
        END;

Here, the trigger checks if the salary value has been changed (using the OLD and NEW keywords). If it has, it inserts a record into the AuditLogs table with the details of the salary change.

Example 3: Creating a DELETE Trigger

This example creates a trigger that fires when an employee record is deleted from the Employees table. The trigger will insert a record into the AuditLogs table to track the deletion.

        CREATE TRIGGER LogEmployeeDeletion
        AFTER DELETE
        ON Employees
        FOR EACH ROW
        BEGIN
            INSERT INTO AuditLogs (Action, TableName, Timestamp)
            VALUES ('DELETE', 'Employees', NOW());
        END;

In this case, the trigger automatically logs the deletion of any employee record into the AuditLogs table.

Managing Triggers

Once a trigger is created, it can be managed using various commands:

  • DROP TRIGGER: Used to delete an existing trigger from the database.
  • SHOW TRIGGERS: Displays a list of all triggers associated with a table.

Example to drop a trigger:

        DROP TRIGGER LogEmployeeInsertion;

Example to show triggers:

        SHOW TRIGGERS LIKE 'Employees';

Conclusion

Triggers are a powerful feature in SQL that allow you to automate actions in response to changes in your database. They are useful for enforcing business rules, maintaining audit logs, and keeping data consistent across multiple tables. By understanding how to create and use triggers, you can enhance the functionality and integrity of your SQL-based applications.




Advertisement