For an instructor lead, in-depth look at learning SQL click below.
Data integrity is a critical aspect in database management. It ensures that our data stays accurate, consistent, and reliable over its entire lifecycle. If the integrity of data is compromised, the implications could be catastrophic for any organization. Hence, maintaining data integrity is paramount. One technique that we can leverage to ensure data integrity is the use of SQL Triggers.
What is an SQL Trigger?
An SQL Trigger is a stored procedure that is automatically executed or fired when a specific event occurs in a table. Triggers can be a very powerful tool in maintaining the integrity of the data in your database. They allow you to enforce complex business rules that cannot be enforced using standard SQL commands.
Types of SQL Triggers
In SQL, there are mainly two types of triggers: DML (Data Manipulation Language) Triggers and DDL (Data Definition Language) Triggers. DML triggers are further divided into three types: AFTER / FOR trigger (Fires after an insert, update or delete operation), INSTEAD OF trigger (Fires instead of an insert, update or delete operation) and INSTEAD OF trigger on a View. In this post, we will focus on DML Triggers.
SQL Trigger Example: the AFTER Trigger
To demonstrate how a trigger works, we will start by creating a simple table called EMPLOYEES:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE EMPLOYEES( emp_id int PRIMARY KEY, First_name varchar(255) NOT NULL, Last_name varchar(255), Start_date date, End_date date, Salary decimal(8, 2), City varchar(255), Description varchar(255) ); |
Now let’s say our business rule requires that any salary increase should be logged. We can create a trigger to do this:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TRIGGER salary_change AFTER UPDATE ON EMPLOYEES FOR EACH ROW WHEN (NEW.Salary > OLD.Salary) BEGIN INSERT INTO LOGS(EMPLOYEE_ID, OLD_SALARY, NEW_SALARY, CHANGE_DATE) VALUES (NEW.emp_id, OLD.Salary, NEW.Salary, NOW()); END; |
In the example above, we created a trigger called salary_change that fires after there is an update on the EMPLOYEES table. This trigger checks if the new salary (NEW.Salary) is greater than the old one (OLD.Salary). If this condition is met, then it will insert a new record in the LOGS table with the employee id, the old salary, the new salary, and the date of the change.
Conclusion
SQL Triggers offer a powerful way of maintaining the integrity of the data in your database by enforcing your business rules effortlessly. As shown in this post, they are simple to implement and use while offering invaluable service of tracking and managing data changes in a timely and efficient way.
Remember, with great power comes great responsibility. Triggers, while powerful, must be used judiciously as they can easily lead to complex dependencies which can be hard to track and manage. Always ensure that you have a good understanding of your triggers and that they are well documented.