Basics of SQL Trigger

Introduction

In this article, we will learn about the Basics of SQL Servers. Triggers are automatic actions that are fired when some event occurs.

Example 1. When we join a new organization, we get a Welcome email from the HR Department. (Frequent job changers will understand this better.)

Example 2. When the salary is credited to an account, we get an SMS/Email.

The sending of welcome emails to every employee on his joining day is done by a person sitting in that organization and monitoring the daily joining formalities.

For sending SMS/Email for every salary deposited every month for every employee, is there a person sitting and doing this manually?

What to think?

Sending an email to every chosen candidate may be easy since not many employees join on the same day except for the bulk of junior hires.

For the second scenario, what if you have 200,000+ employees?

So, whether the data processing task is small or large, one of the easy and automated ways to do this is with a Trigger.

There are 2 types of Triggers.

1. Instead Of Triggers
   • These are executed when you want to execute action 1. 
   • But Instead of executing that action, it will go and execute code written inside this trigger implementation.
2. After Triggers
   • As the name says, these are executed when you are done with certain actions. 
   • Typically they are used for INSERT/UPDATE/DELETE actions.

We will have a look at the syntax and their implementation now.

Now, let's look at the after triggers since they are simple to understand.

-- Create Table with name 'Course'  
CREATE TABLE [dbo].[Course](  
    [CourseID] [int] NOT NULL,  
    [Name] [varchar](50) NULL  
)  
-- Create trigger on table Course  
CREATE TRIGGER Trigger1  
ON [dbo].[Course]  
AFTER INSERT   
AS  
   print 'hi'  
GO  
-- Try inserting data in Course Table and see the output  
INSERT INTO [dbo].[Course](1,'Physics')

The output will be as in the following.

What if you don't want a trigger to be fired for a certain period? Once the triggers are created, we can find them as shown in the following screenshots under the Object Explorer under the table section:

 

We can also programmatically enable/disable triggers with the following syntax.

-- enable / disable trigger  
DISABLE TRIGGER Trigger1 ON Course;  
ENABLE TRIGGER Trigger1 ON Course;

So, similarly, you can create triggers for UPDATE and DELETE as well. And you can test them by firing UPDATE/DELETE commands on the Course Table.

-- Create Update trigger on table Course  
CREATE TRIGGER Trigger2  
ON [dbo].[Course]  
AFTER UPDATE   
AS  
   print 'This is Trigger2 fired after UPDATE'  
GO  
-- Create Delete trigger on table Course  
CREATE TRIGGER Trigger3  
ON [dbo].[Course]  
AFTER DELETE   
AS  
   print ’This is Trigger3 fired after DELETE’  
GO

Now, let's see the Instead of triggers with their syntax and example:

CREATE TRIGGER Trigger4  
ON Course  
Instead of UPDATE  
AS  
   print 'This is instead of trigger for UPDATE'  
GO

Let us update some data in the Course table and see whether an Instead of trigger is fired.

-- Update Name = 'Stats' where CourseID = 1  
UPDATE dbo.Course SET Name = 'Stats' where CourseID = 1

The output is shown below.

Now, this output displays the correct message, but also it says 1 row(s) affected.

So, is my data update done?

Let us check with the following query.

SELECT * FROM dbo.Course

Output

So, my data is not updated; in other words, Trigger4 is fired, and instead of updating the data, it went and printed the message we specified in the Trigger.

Summary

I hope you like this article. This is designed for beginner SQL users who want to get their basic concepts clear. I will be ready with my next chapter soon. Stay tuned. Cheers!

Find more about Triggers in SQL Server here: Triggers in SQL Server.