SQL Triggers are another powerful database object we have at our disposal. In previous articles, we’ve covered user-defined functions, user-defined procedures, and SQL Views. Today we’ll talk about SQL triggers and how to use them to achieve the desired behavior.
The Model
Before we move to the topic of this article, let’s take a quick look at the model we’re using in this article but also throughout this series.
In this article, we’ll focus on DML (data manipulation language) triggers and show how they function when we make changes in a single table.
What Are SQL Triggers?
In SQL Server, triggers are database objects, actually, a special kind of stored procedure, which “reacts” to certain actions we make in the database. The main idea behind triggers is that they always perform an action in case some event happens. If we’re talking about DML triggers, these changes shall be changes in our data. Let’s examine a few interesting situations:
- In case you perform an insert in the call table, you want to update that related customer has 1 more call (in that case, we should have integer attribute in the customer table)
- When you complete a call (update call.end_time attribute value) you want to increase the counter of calls performed by that employee during that day (again, we should have such attribute in the employee table)
- When you try to delete an employee, you want to check if it has related calls. If so, you’ll prevent that delete and raise a custom exception
From examples, you can notice that DML triggers are actions related to the SQL commands defined in these triggers. Since they are similar to stored procedures, you can test values using the IF statement, etc. This provides a lot of flexibility.
The good reason to use DML SQL triggers is the case when you want to assure that a certain control shall be performed before or after the defined statement on the defined table. This could be the case when your code is all over the place, e.g. database is used by different applications, code is written directly in applications and you don’t have it well-documented.
Types of SQL Triggers
In SQL Server, we have 3 groups of triggers:
- DML (data manipulation language) triggers – We’ve already mentioned them, and they react to DML commands. These are – INSERT, UPDATE, and DELETE
- DDL (data definition language) triggers – As expected, triggers of this type shall react to DDL commands like – CREATE, ALTER, and DROP
- Logon triggers – The name says it all. This type reacts to LOGON events
In this article, we’ll focus on DML triggers, because they are most commonly used. We’ll cover the remaining two trigger types in the upcoming articles of this series.
DML Triggers – Syntax
The simplified SQL syntax to define the trigger is as follows.
|
1 2 3 4 5 |
CREATE TRIGGER [schema_name.]trigger_name ON table_name {FOR | AFTER | INSTEAD OF} {[INSERT] [,] [UPDATE] [,] [DELETE]} AS {sql_statements} |
Most of the syntax should be self-explanatory. The main idea is to define:
- A set of {sql_statements} that shall be performed when the trigger is fired (defined by remaining parameters)
- We must define when the trigger is fired. That is what the part {FOR | AFTER | INSTEAD OF} does. If our trigger is defined as FOR | AFTER | INSTEAD OF trigger than SQL statements in the trigger shall run after all actions that fired this trigger is launched successfully. The INSTEAD OF trigger shall perform controls and replace the original action with the action in the trigger, while the FOR | AFTER (they mean the same) trigger shall run additional commands after the original statement has completed
- The part {[INSERT] [,] [UPDATE] [,] [DELETE]} denotes which command actually fires this trigger. We must specify at least one option, but we could use multiple if we need it
With this in mind, we can easily write triggers that will:
- Check (before insert) if all parameters of the INSERT statement are OK, add some if needed, and perform the insert
- After insert, perform additional tasks, like updating a value in another table
- Before delete, check if there are related records
- Update certain values (e.g. log file) after the delete is done
If you want to drop a trigger, you’ll use:
|
1 |
DROP TRIGGER [schema_name.]trigger_name; |
SQL INSERT Trigger – Example
First, we’ll create a simple SQL trigger that shall perform check before the INSERT statement.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
DROP TRIGGER IF EXISTS t_country_insert; GO CREATE TRIGGER t_country_insert ON country INSTEAD OF INSERT AS BEGIN DECLARE @country_name CHAR(128); DECLARE @country_name_eng CHAR(128); DECLARE @country_code CHAR(8); SELECT @country_name = country_name, @country_name_eng = country_name_eng, @country_code = country_code FROM INSERTED; IF @country_name IS NULL SET @country_name = @country_name_eng; IF @country_name_eng IS NULL SET @country_name_eng = @country_name; INSERT INTO country (country_name, country_name_eng, country_code) VALUES (@country_name, @country_name_eng, @country_code); END; |
We can see our trigger in the Object Explorer, when we expand the data for the related table (country).
I want to emphasize a few things here:
- The INSERT statement fires this query and is actually replaced (INSTEAD OF INSERT) with the statement in this trigger
- We’ve defined a number of local variables to store values from the original insert record (INSERTED). This record is specific for triggers and it allows you to access this single record and its’ values
- Note: The INSERTED record can be used in the insert and update SQL triggers.
- With IF statements, we’ve tested values and SET values if they were not set before
- At the end of the query, we performed the INSERT statement (the one replacing the original one that fired this trigger)
Let’s now run an INSERT INTO command and see what happens in the database. We’ll run the following statements:
|
1 2 3 |
SELECT * FROM country; INSERT INTO country (country_name_eng, country_code) VALUES ('United Kingdom', 'UK'); SELECT * FROM country; |
The result is in the picture below.
You can easily notice that the row with id = 10, had been inserted. We haven’t specified the country_name, but the trigger did its’ job and filled that value with country_name_eng.
- Note: If the trigger is defined on a certain table, for a certain action, it shall always run when this action is performed.
SQL DELETE Trigger – Example
Now let’s create a trigger that shall fire upon the DELETE statement on the country table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DROP TRIGGER IF EXISTS t_country_delete; GO CREATE TRIGGER t_country_delete ON country INSTEAD OF DELETE AS BEGIN DECLARE @id INT; DECLARE @count INT; SELECT @id = id FROM DELETED; SELECT @count = COUNT(*) FROM city WHERE country_id = @id; IF @count = 0 DELETE FROM country WHERE id = @id; ELSE THROW 51000, 'can not delete - country is referenced in other tables', 1; END; |
For this trigger, it’s worth to emphasize the following:
- Once again, we perform the action before (instead of) actual executing (INSTEAD OF DELETE)
- We’ve used record DELETED. This record can be used in the triggers related to the DELETE statement
- Note: The DELETED record can be used in delete and update SQL triggers.
- We’ve used the IF statement to determine if the row should or shouldn’t be deleted. If it should, we’ve performed the DELETE statement, and if shouldn’t, we’re thrown and exception
Running the below statement went without an error because the country with id = 6 had no related records.
|
1 |
DELETE FROM country WHERE id = 6; |
If we run this statement we’ll see a custom error message, as shown in the picture below.
|
1 |
DELETE FROM country WHERE id = 1; |
Such a message is not only descriptive, but allows us to treat this error nicely and show a more meaningful message to the end-user.
SQL UPDATE Trigger
I will leave this one to you, as a practice. So try to write down the UPDATE trigger. The important thing you should know is that in the update trigger you can use both – INSERTED (after update) and DELETED (before update) records. In almost all cases, you’ll need to use both of them.
When to Use SQL Triggers?
Triggers share a lot in common with stored procedures. Still, compared to stored procedures they are limited in what you can do. Therefore, I prefer to have one stored procedure for insert/update/delete and make all checks and additional actions there.
Still, that is not always the option. If you inherited a system or you simply don’t want to put all the logic in the stored procedures, then triggers could a solution for many problems you might have.
Table of contents
His past and present engagements vary from database design and coding to teaching, consulting, and writing about databases. Also not to forget, BI, creating algorithms, chess, philately, 2 dogs, 2 cats, 1 wife, 1 baby...
You can find him on LinkedIn
View all posts by Emil Drkusic
- Learn SQL: How to prevent SQL Injection attacks - May 17, 2021
- Learn SQL: Dynamic SQL - March 3, 2021
- Learn SQL: SQL Injection - November 2, 2020