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’, ‘firstname.lastname@example.org’;
2. Insert into Staff(name, email) select ‘Anjali’, ‘email@example.com’;
3. Insert into Staff(name, email) select ‘Pooja’, ‘firstname.lastname@example.org’;
4. Insert into Staff(name, email) select ‘Mohit’, ‘email@example.com’;
5. Insert into Staff(name, email) select ‘Vipul’, ‘firstname.lastname@example.org’;
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
An Example of Data Definition Language Trigger :
We are creating a trigger on table drop and alter in a Database.
1. USE SomeSampleDB
3. CREATE TRIGGER DDLTriggerToBlkDrpAltrTbl
4. ON DATABASE
5. FOR DROP_TABLE, ALTER_TABLE
8. PRINT ‘The DDLTriggerToBlkDrpAltrTbl is not giving you permission to drop or alter the table.’
When we will write following command on our “Staff” Table created above :
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 :
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
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
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.
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
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
9. PRINT ‘You are not authorized to login, as you already have two active user sessions’
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.