Database Triggers Types and Examples

Database triggers are special stored procedures that are automatically executed or fired when certain events occur in a database. They are typically used to enforce business rules, maintain data integrity, audit changes, or perform automated actions. Triggers can be defined to execute before or after an INSERT, UPDATE, or DELETE operation on a table.

Types of Database Triggers

• BEFORE Triggers:
  • Executed before the triggering event (INSERT, UPDATE, DELETE).
  • Often used to validate or modify data before it is saved to the table.
• AFTER Triggers:
  • Executed after the triggering event.
  • Commonly used for auditing, logging, or updating other tables.
• INSTEAD OF Triggers:
  • Executed in place of the triggering event.
  • Typically used with views to allow modifications that would otherwise not be possible.

Database Trigger Examples

 BEFORE INSERT Trigger

This trigger ensures that the salary column in the employees table is always positive before inserting a new record.

CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < 0 THEN
        SET NEW.salary = 0;
    END IF;
END;

AFTER INSERT Trigger

This trigger logs a new entry into an audit_log table whenever a new employee is added.

CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (action, employee_id, action_time)
    VALUES ('INSERT', NEW.employee_id, NOW());
END;

BEFORE UPDATE Trigger

This trigger prevents updating the salary column to a negative value.

CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary cannot be negative';
    END IF;
END;

AFTER UPDATE Trigger

This trigger logs changes to the employees table in an audit_log table.

CREATE TRIGGER after_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (action, employee_id, action_time)
    VALUES ('UPDATE', OLD.employee_id, NOW());
END;

BEFORE DELETE Trigger

This trigger prevents deleting an employee if they are assigned to a project.

CREATE TRIGGER before_employee_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    IF EXISTS (SELECT 1 FROM projects WHERE assigned_employee_id = OLD.employee_id) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete employee assigned to a project';
    END IF;
END;

AFTER DELETE Trigger

This trigger logs the deletion of an employee in the audit_log table.

CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (action, employee_id, action_time)
    VALUES ('DELETE', OLD.employee_id, NOW());
END;

 INSTEAD OF Trigger

This trigger allows inserting data into a view that is based on multiple tables.

CREATE TRIGGER instead_of_insert_employee_view
INSTEAD OF INSERT ON employee_view
FOR EACH ROW
BEGIN
    INSERT INTO employees (employee_id, name, salary)
    VALUES (NEW.employee_id, NEW.name, NEW.salary);

    INSERT INTO employee_details (employee_id, department, position)
    VALUES (NEW.employee_id, NEW.department, NEW.position);
END;

Key Points to Remember about Database Triggers

• Triggers are associated with a specific table and event (INSERT, UPDATE, DELETE).
• Use NEW to refer to the new row being inserted or updated.
• Use OLD to refer to the existing row being updated or deleted.
• Triggers can be defined to execute for each row (FOR EACH ROW) or once per statement.
• Be cautious with triggers, as they can lead to performance issues if overused or poorly designed.

These examples demonstrate how triggers can be used to enforce rules, maintain data integrity, and automate tasks in a database.