7.2 Using Triggers for Automated Actions

7.2.1 Introduction to Triggers

A Trigger is a special type of stored procedure that automatically executes (or "fires") when certain events occur in the database. Triggers are typically used to enforce business rules, maintain data integrity, or perform automated actions without requiring explicit calls from the application. They can be triggered by events such as INSERT, UPDATE, or DELETE operations.

• Definition: A trigger is a set of SQL statements executed automatically in response to certain events.
• Purpose: Triggers are used to automate tasks like data validation, logging, auditing, and more.

7.2.2 Types of Triggers

There are several types of triggers, typically classified based on the event that triggers them:

• BEFORE Trigger: Fires before an insert, update, or delete operation is executed.
  • Used to validate or modify data before it is written to the table.
• AFTER Trigger: Fires after an insert, update, or delete operation has been executed.
  • Used for auditing, logging, or making changes based on the data that was inserted, updated, or deleted.
• INSTEAD OF Trigger: Replaces the standard operation (insert, update, or delete) with the logic defined in the trigger. Often used to modify or redirect operations on views.

7.2.3 Syntax for Creating Triggers

The syntax for creating triggers depends on the SQL database system (MySQL, SQL Server, etc.), but here is a general structure:

CREATE TRIGGER trigger_name
    {BEFORE|AFTER|INSTEAD OF} {INSERT|UPDATE|DELETE}
    ON table_name
    FOR EACH ROW
    BEGIN
        -- SQL statements to be executed
    END;

• trigger_name: The name of the trigger.
• BEFORE/AFTER/INSTEAD OF: Specifies when the trigger should fire.
• INSERT/UPDATE/DELETE: Specifies the type of operation that triggers the execution.
• table_name: The table on which the trigger is defined.
• FOR EACH ROW: Specifies that the trigger will act on each row affected by the operation.

7.2.4 Example: BEFORE INSERT Trigger

A BEFORE INSERT trigger can be used to validate data before it is inserted into a table. For instance, preventing negative values in a salary column:

CREATE TRIGGER PreventNegativeSalary
BEFORE INSERT ON Employee
FOR EACH ROW
BEGIN
    IF NEW.Salary < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary cannot be negative';
    END IF;
END;

• Explanation: The trigger PreventNegativeSalary ensures that any INSERT operation on the Employee table fails if the Salary is negative. If a negative salary value is detected, an error message is raised.

7.2.5 Example: AFTER UPDATE Trigger

An AFTER UPDATE trigger can be used to log changes made to a table. For example, tracking salary changes in an audit table:

CREATE TRIGGER LogSalaryChanges
AFTER UPDATE ON Employee
FOR EACH ROW
BEGIN
    IF OLD.Salary <> NEW.Salary THEN
        INSERT INTO SalaryAudit (EmployeeID, OldSalary, NewSalary, ChangeDate)
        VALUES (NEW.EmployeeID, OLD.Salary, NEW.Salary, NOW());
    END IF;
END;

• Explanation: The LogSalaryChanges trigger fires after an UPDATE operation on the Employee table. It checks if the salary has changed (by comparing the OLD and NEW values) and logs the change into the SalaryAudit table.

7.2.6 Example: INSTEAD OF Trigger on a View

An INSTEAD OF trigger can be used on views to modify the behavior of INSERT, UPDATE, or DELETE operations. For example, redirecting an update on a view to update the underlying tables:

CREATE TRIGGER UpdateEmployeeDetails
INSTEAD OF UPDATE ON EmployeeView
FOR EACH ROW
BEGIN
    UPDATE Employee
    SET Salary = NEW.Salary
    WHERE EmployeeID = NEW.EmployeeID;
END;

• Explanation: The UpdateEmployeeDetails trigger intercepts updates to the EmployeeView and instead performs the update on the underlying Employee table.

7.2.7 Triggering Actions Based on DELETE

You can use triggers to handle actions like cleaning up related records when data is deleted. For example, automatically deleting associated records from a Bonus table when an employee is removed:

CREATE TRIGGER DeleteEmployeeBonuses
AFTER DELETE ON Employee
FOR EACH ROW
BEGIN
    DELETE FROM Bonus WHERE EmployeeID = OLD.EmployeeID;
END;

• Explanation: The DeleteEmployeeBonuses trigger automatically deletes any bonuses associated with an employee when that employee's record is deleted from the Employee table.

7.2.8 Managing Triggers

• Viewing Triggers: In SQL databases, you can view existing triggers using commands like SHOW TRIGGERS in MySQL or querying system views in SQL Server.
• Dropping Triggers: To remove a trigger from a table, you can use the DROP TRIGGER statement:

  DROP TRIGGER trigger_name;
  

Conclusion:

Triggers are a powerful tool for automating database actions and ensuring data integrity. They allow you to perform actions such as validation, logging, auditing, and cascading changes automatically in response to data modifications. By using triggers effectively, you can enforce business rules and improve database performance while minimizing the need for manual intervention. However, triggers should be used with caution as they can increase the complexity of your database and introduce unintended side effects if not managed properly.