Understanding MySQL BEFORE UPDATE Triggers for Data Integrity

What is a Trigger?

• A trigger is a stored procedure that automatically runs (or "fires") when specific events occur in a database.
• Triggers can be used to enforce business rules, validate data, or maintain data integrity.

BEFORE UPDATE Trigger

• A BEFORE UPDATE trigger is executed before an UPDATE operation is performed on a table.
• It allows you to modify the values of fields prior to their update in the database.

Key Concepts

• Syntax: The basic syntax for creating a BEFORE UPDATE trigger is:
• FOR EACH ROW: This indicates that the trigger will execute for each row that is being updated.
• OLD and NEW: Inside the trigger, refer to the old values of the row using OLD.column_name and the new values using NEW.column_name.

CREATE TRIGGER trigger_name
BEFORE UPDATE ON table_name
FOR EACH ROW
BEGIN
    -- Trigger logic here
END;

Use Cases

• Validation: Ensure that certain conditions are met before allowing an update.
• Automatic Calculations: Update a field based on the new values of other fields.
• Audit Trails: Keep track of changes made to important fields.

Example

Creating a Table

CREATE TABLE Employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

Creating a BEFORE UPDATE Trigger

CREATE TRIGGER before_salary_update
BEFORE UPDATE ON Employees
FOR EACH ROW
BEGIN
    IF NEW.salary < OLD.salary THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Salary cannot be decreased';
    END IF;
END;

Explanation of the Example

• The trigger before_salary_update checks if the new salary (NEW.salary) is less than the old salary (OLD.salary).
• If the condition is true, it raises an error, preventing the salary from being decreased.

Conclusion

• BEFORE UPDATE triggers are powerful tools in MySQL that help maintain data integrity and enforce business rules.
• They provide a mechanism to execute custom logic before changes are made to the database, ensuring data accuracy and consistency.

By understanding and utilizing BEFORE UPDATE triggers, you can significantly enhance the functionality of your MySQL database applications.