Executive Summary:
Triggers are an indispensable tool in SQL which allow for automatic execution of specific operations based on database events. Triggers can be instrumental in enforcing business rules and maintaining data integrity in your database. I’ll quickly review what triggers are, explore a few types & how to create them, and look at a few scenarios where they are commonly used.
Introduction:
In SQL, triggers are stored procedures that automatically fire, or execute, when a specific event occurs. Those event can be an INSERT, UPDATE, or DELETE operation on a specific table. They are critical to maintain consistent, accurate, and reliable data in your database. Let’s review some examples.
Validating UPDATE Values
-- Create Trigger to reject invalid salaries
CREATE TRIGGER salary_check
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF NEW.salary < 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Cannot have negative salary';
END IF;
END;
Handling Errors in Triggers:
Errors should be handled rather carefully to prevent any unexpected behavior. In the example below, notice the order of operations is important. Notice that
CREATE TRIGGER insert_check
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'An error occurred during operation';
END;
END;Conclusions:
Understanding SQL Triggers is critical for maintaining data integrity in your database. The automatic stored procedures that are automatically triggered, help enforce business rules and consistency. Having a firm understanding of triggers will allow you to more gracefully handle error states and prevent invalid data from being committed.