Basics of SQL Trigger

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 a SMS/Email.

The sending of welcome emails to each and 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 an easy task since not many employees join on the same day except for the bulk of junior hirings.

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

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

Let's first look at the after triggers now 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:

                                            

Once the triggers are created we can find them as shown in the following screenshots under the Object Explorer under the table section:

                                              

What if for a certain period of time you don't want a trigger to be fired?

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, in a similar way 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 as 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 that we have specified in the Trigger.

I hope you like this article. This is designed for those people that are beginners to SQL and want to get their basic concepts clear.

I will be ready with my next chapter soon. Stay tuned. Cheers!