Emil Drkusic
SQL Triggers - the data model we'll use in the article

Learn SQL: SQL Triggers

March 20, 2020 by

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.

SQL Triggers - the data model we'll use in the article

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.

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:

SQL INSERT Trigger – Example

First, we’ll create a simple SQL trigger that shall perform check before the INSERT statement.

We can see our trigger in the Object Explorer, when we expand the data for the related table (country).

object explore & triggers

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:

The result is in the picture below.

the result of the insert statement

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.

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.

If we run this statement we’ll see a custom error message, as shown in the picture below.

the error message thrown by the SQL trigger

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

Learn SQL: CREATE DATABASE & CREATE TABLE Operations
Learn SQL: INSERT INTO TABLE
Learn SQL: Primary Key
Learn SQL: Foreign Key
Learn SQL: SELECT statement
Learn SQL: INNER JOIN vs LEFT JOIN
Learn SQL: SQL Scripts
Learn SQL: Types of relations
Learn SQL: Join multiple tables
Learn SQL: Aggregate Functions
Learn SQL: How to Write a Complex SELECT Query?
Learn SQL: The INFORMATION_SCHEMA Database
Learn SQL: SQL Data Types
Learn SQL: Set Theory
Learn SQL: User-Defined Functions
Learn SQL: User-Defined Stored Procedures
Learn SQL: SQL Triggers
Learn SQL: Practice SQL Queries

Emil Drkusic
Latest posts by Emil Drkusic (see all)
SQL commands, T-SQL

About Emil Drkusic

Emil is a database professional with 10+ years of experience in everything related to databases. During the years, he worked in the IT and finance industry and now works as a freelancer. 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

132 Views