Trigger in SQL

Trigger in SQL

Trigger in SQL. After learning about Stored Procedures in our last article. Its time to talk about Triggers in SQL. The concept of triggers is very powerful provided by Microsoft because it is related to the security of data. A trigger simply means a button. Behind every button, there is an action that gets performed when that trigger is pressed.

In SQL, you can set a trigger that goes off when some conditions are met. Let us understand triggers with an example.

Use case Scenario of Triggers

Suppose you are working in an office where you take care of databases. You are the one who is responsible for the security of data. Your boss comes to you and tells you, there are a lot of hacking attempts are going on nowadays. I am concerned about the data that we have.

He says, he wants to know if someone tries to make some hacking attempt into our data and want to stop the attempt too. He said our daily sales are approximately 10000$ and the maximum it goes 20000$. If someday the daily sales go 1 Million then I want that transaction to be rolled back and an email should be sent to a list of email addresses, also this suspicious transaction should be recorded in logs. So that we can investigate it later on.

This problem can be solved easily using triggers in SQL. The triggers can keep an eye on the data being stored an in case of any suspicious activity they can roll back the transaction and make logs about the transaction.

One thing you that you will notice here, the trigger is being used when some sort of insert query is running. You can also create triggers for delete action, update action.

You can image a trigger in SQL as automated scripts that get executed when some undesired/suspicious activity happens.

How to create a Trigger in SQL

We will be using Adventureworks again in this practice. The table we will take this time is HumanResource.Shift.

Step 1

Select the right database before creating the trigger, so that the trigger gets created inside the right database. Use the following query.

use [AdventureWorks2019]

Step 2

See the data in the table so that you can see the table has no new record created. Use the following query.

select * from [HumanResources].[Shift]

Step 3

Create Trigger by executing the query below. You can see the name of this trigger is StopInsert. The table this is going to be applied is “[HumanResources].[Shift]“. The action this trigger will act on is Insert. The “As” is a part of the syntax. The Print keyword is printing the message for the person who is making the entry.

Rollback Transaction is stopping the transaction. The end keyword is telling the SSMS that the query ends here. The Go keyword tells the SSMS to start this trigger.

Create Trigger StopInsert
On [HumanResources].[Shift]
After Insert
As
Begin
Print 'New Entries are not allowed, Please contact your manager'
Rollback transaction
end
go

The trigger has been created successfully if you see a success message on the message center below.

Step 4

Insert the data in the table and see if the trigger is working properly or not.

Insert into [HumanResources].[Shift]
(
[Name],[StartTime],[EndTime],[ModifiedDate]
)
values
(
'John','05:00:00.0000000','16:00:00.0000000','2018-04-30 00:00:00.000'
)

Step 5

Check the data if it has been inserted.

select * from [HumanResources].[Shift]

You would see the same table without any new records. This is because of the trigger. The above trigger is created for stopping the insert action in the table. This can be for delete, drop, update, Sending Email etcetera. The possibilities are endless.

I hope the article was helpful. Feel free to comment if you have any feedback or suggestions.

Take Care Guys!