What are TRIGGERS in SQL – Server

What are TRIGGERS in SQL – Server

What are TRIGGERS in SQL - Server

We are going to discuss about TRIGGERS in SQL – SERVER in this article. Triggers are very important in enterprises level database management. You have to understand following table:
This command will create a table on which we will apply triggers:

1. Create table Staff(id int NOT NULL PRIMARY KEY IDENTITY(1, 1), name varchar(55), email varchar(55));
2. Create table Log_Audit(id int NOT NULL PRIMARY KEY IDENTITY(1, 1), StfId int, StfName varchar(50), StfEmail varchar(50), AuditAction varchar(50), AuditTime datetime);
Fill your Staff table with the following data:
1. Insert into Staff(name, email) select ‘Rajat’, ‘rajat@example.com’;
2. Insert into Staff(name, email) select ‘Anjali’, ‘anjali@example.com’;
3. Insert into Staff(name, email) select ‘Pooja’, ‘pooja@example.com’;
4. Insert into Staff(name, email) select ‘Mohit’, ‘mohit@example.com’;
5. Insert into Staff(name, email) select ‘Vipul’, ‘vipul@example.com’;

In every database some events occur and when an event occurs some special kind of stored procedures are executed automatically. These special kind of stored procedures are TRIGGERS.
A stored procedure can be called by Triggers. Auditing becomes very easy and accurate while using triggers. Triggers are defined on associated event of a database, table, view or schema

What are different kinds of triggers?

1. Data Definition Language Triggers – Statements like create, alter, drop etc.
2. DML Triggers
3. CLR Triggers
4. LOGON Triggers

How to write DDL Triggers ?

1. CREATE TRIGGER desiredTriggerName
2. ON DDLNameofevent
3. FOR CREATE_DATABASE
4. AS
5. …

An Example of Data Definition Language Trigger :
We are creating a trigger on table drop and alter in a Database.
1. USE SomeSampleDB
2. GO
3. CREATE TRIGGER DDLTriggerToBlkDrpAltrTbl
4. ON DATABASE
5. FOR DROP_TABLE, ALTER_TABLE
6. AS
7. BEGIN
8. PRINT ‘The DDLTriggerToBlkDrpAltrTbl is not giving you permission to drop or alter the table.’
9. ROLLBACK
10. END
When we will write following command on our “Staff” Table created above :
USE SomeSampleDB
Drop table Staff
It will show you a message – “The DDLTriggerToBlkDrpAltrTbl is not giving you permission to drop or alter the table.”
If we will try to alter the table with commands as follows :
USE SomeSampleDB
Alter table Staff
Add Salary int

You will again see the same message given above.

DDL_DATABASE_LEVEL_EVENTS lets our trigger deal with all DDL events.

 

Data Manipulation Language triggers

DML triggers are created on DML statements: insert, update & delete. DML triggers can be further classified as
A. After trigger – After Completed Execution of SQL Server Query.
A FOR clause or AFTER clause is used in this trigger.

How to write After Trigger (DML)
1. CREATE TRIGGER ON MY_TABLE
2. FOR DML_Events
3. AS
4. ………………………………

B. Instead of trigger – Such triggers are fired before execution of a SQL Statement
In this trigger, we use INSTEAD OF clause.
How to write :
1. CREATE TRIGGER ON MY_TABLE
2. INSTEAD OF DML_Events
3. AS
4. ………………………………
Just as in above example FOR clause is used in instead of trigger INSTEAD OF clause is used.

Common Language Runtime Triggers

SQL Server is integrated with .NET framework, and .NET framework has languages. This trigger can be created by any of those languages.

Logon triggers

Auditing of Login Activity can be done using LOGON Triggers. Number of user Login in a particular session can also be controlled using this trigger. SQL Server has a LOGON event and it is easy to understand that this trigger can be triggered at that event.
How to Write LOGON Triggers?
1. CREATE TRIGGER ConcurrentConnectionLimit
2. ON ALL SERVER WITH EXECUTE AS ‘sa’
3. FOR LOGON
4. AS
5. BEGIN
6. IF ORIGINAL_LOGIN() = ‘TestUser’
7. AND(SELECT COUNT( * ) FROM sys.dm_exec_sessions WHERE Is_User_Process = 1 AND Original_Login_Name = ‘TestUser’) > 2
8. BEGIN
9. PRINT ‘You are not authorized to login, as you already have two active user sessions’
10. ROLLBACK
11. END
12. END

TRIGGERS in SQL – SERVER | How to Create Triggers in C#
Now, when you try to open more than 2 concurrent sessions for the ‘TestUser’, you will get the message given below.
2 Concurrent sessions for the user used above are allowed. For more CC Sessions :
“Logon Failed for Login “USERNAME” due to trigger execution.

Feel free to express your views and doubts in the comment section given below.

Social Share

Leave a Reply

Your email address will not be published. Required fields are marked *