What is a Trigger in SQL?

Posted on by Ian

In SQL databases, triggers can play a significant role in automating responses to specific events within a database. Whether we’re dealing with data validation, logging changes, or enforcing business rules, SQL triggers can be a handy tool to ensure that certain actions are taken automatically when certain conditions are met.

This article covers what SQL triggers are, how they work, and provides an example to illustrate their use.

What is a Trigger in SQL?

A trigger in SQL is a named database object that automatically executes or fires when a particular event occurs in the database. These events typically involve data manipulation operations such as INSERT, UPDATE, or DELETE on a table. Triggers are often used to enforce business rules, maintain data integrity, and automate tasks that would otherwise require manual intervention.

Types of Triggers

  • Before Triggers: These triggers execute before the data modification takes place. They are commonly used to validate or modify data before it’s committed to the database.
  • After Triggers: These triggers execute after the data modification has been completed. They are typically used for logging purposes or to update other tables based on the changes made.
  • Instead Of Triggers: These triggers are used in place of the original operation, effectively replacing the triggering action with a custom one. They are often used with views to enable complex operations that would otherwise be impossible with a simple INSERT, UPDATE, or DELETE.

How Triggers Work

Triggers are bound to a specific table and are automatically invoked when the triggering event occurs on that table. When the specified event happens (such as inserting a new row), the trigger is activated, and the predefined SQL code within the trigger is executed. This allows for automated processes like:

  • Enforcing complex business rules
  • Auditing changes in data
  • Synchronizing tables
  • Validating input data

Triggers can reference old and new data values, making it possible to compare the state of the data before and after the event.

Trigger Syntax

The precise syntax for creating and modifying triggers varies between RDBMSs. That said, they tend to follow the same general outline. The MySQL syntax provides a pretty good picture of how it goes:

CREATE
    [DEFINER = user]
    TRIGGER [IF NOT EXISTS] trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

MySQL doesn’t support the INSTEAD OF trigger (at least of this writing), and so that’s not listed here. Other RDBMSs, such as SQL Server and PostgreSQL support the INSTEAD OF trigger, and so that’s an additional option to BEFORE | AFTER.

Example of a Trigger in SQL

Let’s consider a scenario where we want to automatically log any changes made to an Employees table. We will create an AFTER UPDATE trigger that inserts a record into an EmployeeAudit table whenever an update occurs.

Step 1: Create the Employees Table and Insert Data

-- Create the Employees table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Salary DECIMAL(10, 2)
);

-- Insert Data
INSERT INTO Employees (EmployeeID, FirstName, LastName, Salary)
VALUES
    (1, 'Hazel', 'Blakely', 60000),
    (2, 'Butch', 'Felding', 65000),
    (3, 'Hamish', 'Parsley', 70000);

Step 2: Create the EmployeeAudit Table

CREATE TABLE EmployeeAudit (
    AuditID INT PRIMARY KEY IDENTITY(1,1),
    EmployeeID INT,
    OldFirstName VARCHAR(50),
    NewFirstName VARCHAR(50),
    OldLastName VARCHAR(50),
    NewLastName VARCHAR(50),
    OldSalary DECIMAL(10, 2),
    NewSalary DECIMAL(10, 2),
    ModifiedDate DATETIME DEFAULT GETDATE()
);

Step 3: Create the Trigger

CREATE TRIGGER trg_AfterEmployeeUpdate
ON Employees
AFTER UPDATE
AS
BEGIN
    INSERT INTO EmployeeAudit (
        EmployeeID, 
        OldFirstName, NewFirstName, 
        OldLastName, NewLastName, 
        OldSalary, NewSalary
    )
    SELECT 
        inserted.EmployeeID,
        deleted.FirstName AS OldFirstName,
        inserted.FirstName AS NewFirstName,
        deleted.LastName AS OldLastName,
        inserted.LastName AS NewLastName,
        deleted.Salary AS OldSalary,
        inserted.Salary AS NewSalary
    FROM 
        inserted
    INNER JOIN 
        deleted ON inserted.EmployeeID = deleted.EmployeeID;
END;

Step 4: Test the Trigger

-- Update the Employees table
UPDATE Employees
SET FirstName = 'Hazy', LastName = 'Simper', Salary = 80000
WHERE EmployeeID = 1;

-- Check the trigger result
SELECT * FROM EmployeeAudit;

Result:

AuditID  EmployeeID  OldFirstName  NewFirstName  OldLastName  NewLastName  OldSalary  NewSalary  ModifiedDate            
-------  ----------  ------------  ------------  -----------  -----------  ---------  ---------  ------------------------
1        1           Hazel         Hazy          Blakely      Simper       60000      80000      2024-08-27T22:24:49.480Z

We can see that after running the update statement, the trigger fired automatically and inserted a row into the EmployeeAudit table containing the old and new information for the updated employee.

Advantages of Using Triggers

Here are some of the advantages of using triggers:

  • Automation: Triggers automatically enforce rules and run tasks without human intervention.
  • Consistency: They help maintain consistent data across different tables by automating updates.
  • Security: Triggers can prevent unauthorized changes by checking conditions before data is modified.
  • Efficiency: By reducing the need for manual checks and updates, triggers can enhance the performance of database operations.

Disadvantages of Using Triggers

We should also be mindful of the disadvantages before deciding whether or not to introduce a new trigger into the database. Here are a few of the main disadvantages:

  • Complexity: Triggers can add complexity to the database, making it harder to debug and maintain.
  • Performance Impact: Poorly designed triggers can slow down database operations, especially if they are too complex or if they cascade (one trigger causes another trigger to fire).
  • Hidden Logic: Since triggers operate automatically, they can make the flow of data manipulation less transparent to developers.

When to Use Triggers

Triggers are best used in situations where:

  • Data integrity needs to be maintained across tables without manual intervention.
  • Audit trails for changes to data are necessary.
  • Complex business rules require enforcement automatically upon data changes.

However, they should be used judiciously to avoid performance issues and unnecessary complexity.

Conclusion

SQL triggers are a powerful feature that can automate tasks, enforce business rules, and maintain data integrity within a database. While they aren’t without their disadvantages, they can certainly be a handy tool for the toolkit.