Understanding SQL Triggers in simple and easy way with more practical examples
A trigger is special type of stored procedure that automatically run when insert event or update event or delete event occurs in database server(for eg:-during insert,update or delete)
Types of Triggers
We have 3 types of triggers.
CREATE TRIGGER tr_sqldemo on USERS
print 'insert trigger fired‘
CREATE TRIGGER tr_updsqldemo on USERS
print 'update trigger fired'
Data Definition Language(DDL) triggers
Data Manipulation Language(DML) triggers
These triggers are created for DDL statements such as CREATE,ALTER AND DROP
These triggers are created for DML statements such as INSERT,UPDATE AND DETLETE
There are 2 types of triggers.
After Trigger(using FOR/AFTER CLAUSE) :
The After trigger fires after SQL server finishes the action.
For Example: if you insert a record into a table then the trigger related with insert event on this table will fire.
Instead of Trigger:
The instead of trigger fires before the action executes.
We can an INSTED OF insert,update,delete on a table.
For example, if you insert a record into table then instead of action will execute before inserting record into the table.
The Logon trigger is a special kind of stored procedure which will fire automatically to LOGON event .
The logon trigger is fired only after successful authentication,it wont fire if the authentication failed.
What is the use of Logon trigger?
It is commonly used for audit activity such as
a)Tracking the Login activity
b)Limiting the number of concurrent sessions for single user
c)Restricting SQL server login based on the time,hostname or application names