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)
EXAMPLE:
CREATE TRIGGER tr_sqldemo on USERS
FOR INSERT
as
begin
print 'insert trigger fired‘
End
CREATE TRIGGER tr_updsqldemo on USERS
FOR UPDATE
as
begin
print 'update trigger fired'
end
Types of Triggers
We have 3 types of triggers.
DDL Triggers:
These triggers are created for DDL statements such as CREATE,ALTER AND DROP
DML Triggers:
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.
Logon Triggers:
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