SQL Trigger
Trigger allows us to execute a batch of SQL command (or database object or PL/SQL block) on
either insert, update or delete operation on a specified table.
It is a special type of procedure which is executed on
insert , update or delete operation.
Types of Trigger
There are four types of trigger
1) DDL
trigger
2) DML
trigger
3) CLR
trigger
4) Logon
trigger
DDL Trigger
DDL trigger is executed on data definition language
statements like create, alter and drop statement.
It is also executed on certain system defined stored
procedures like create login
statement and sp_addlogin stored procedure.
DML Trigger
DML trigger is
executed on data manipulation language statement like Insert, Update and Delete
command and the stored procedures that define the DML command.
There are two types of DML command.
1) After
trigger (using for/after clause)
2) Instead
of trigger (using instead of clause)
After Trigger
This type of trigger is get executed after the SQL server
successfully finishes the execution of PL/SQL block.
Example: If you insert record/row in a table then the
trigger related/associated with the insert event on this table will fire only
after the row passes all the constraints, like as primary key constraint, and
some rules. If the record/row insertion fails,
SQL Server will not fire the After Trigger.
Before Trigger
This type of trigger fires before SQL Server starts the
execution of the action that fired it. This is differing from the AFTER
trigger, which fires after the action that caused it to fire. We can have an
INSTEAD OF insert/update/delete trigger on a table that successfully executed
but does not include the actual insert/update/delete to the table.
Example: If you insert record/row in a table then the
trigger related/associated with the insert event on this table will fire before
the row passes all the constraints, such as primary key constraint and some
rules. If the record/row insertion fails, SQL Server will fire the Instead of
Trigger.
CLR Trigger
CLR triggers are special type of triggers that based on the
CLR (Common Language Runtime) in .net framework. CLR integration of triggers
has been introduced with SQL Server 2008 and allows for triggers to be coded in
one of .NET languages like C#, Visual Basic and F#. We coded the objects (like
trigger) in the CLR that have heavy computations or need references to objects
outside the SQL Server. We can write code for both DDL and DML triggers, using
a supported CLR language like C#, Visual basic and F#.
Logon Trigger
Logon triggers are special type of trigger that fire when
LOGON event of Sql Server is raised. This event is raised when a user session
is being established with Sql Server that is made after the authentication
phase finishes, but before the user session is actually established. Hence, all
messages that we define
In the trigger such as error messages, will be redirected to
the SQL Server error log. Logon triggers do not fire if authentication fails.
We can use these triggers to audit and control server sessions, such as to
track login activity or limit the number of sessions for a specific login.
CREATE
TRIGGER trigger_name
ON
ALL SERVER
[WITH
ENCRYPTION]
{FOR|AFTER}
LOGON
AS
sql_statement
[1...n ]
Syntax
1. CREATE TRIGGER trigger_name
2. ON {table|view}
3. [WITH ENCRYPTION|EXECUTE AS]
4. {FOR|AFTER|INSTEAD OF} {[CREATE|ALTER|DROP|INSERT|UPDATE|DELETE ]}
5. [NOT FOR REPLICATION]
6. AS
7. sql_statement [1...n ]
AFTER triggers cannot be defined on views.
Trigger
Instead-of-Trigger-Example.html
CREATE TABLE Employee_Demo
(
Emp_ID int identity,
Emp_Name varchar(55),
Emp_Sal decimal (10,2)
)
Insert into Employee_Demo values ('Amit',1000);
Insert into Employee_Demo values ('Mohan',1200);
Insert into Employee_Demo values ('Avin',1100);
Insert into Employee_Demo values ('Manoj',1300);
Insert into Employee_Demo values ('Riyaz',1400);
create table Employee_Demo_Audit
(
Emp_ID int,
Emp_Name varchar(55),
Emp_Sal decimal(10,2),
Audit_Action varchar(100),
Audit_Timestamp datetime
)
CREATE TRIGGER trgAfterInsert on
Employee_Demo
FOR INSERT
AS declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;
set
@audit_action='Inserted
Record -- After Insert Trigger.'; insert into
Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values
(@empid,@empname,@empsal,@audit_action,getdate());
PRINT 'AFTER INSERT trigger fired.'
insert into Employee_Demo(Emp_Name,Emp_Sal)values ('Shailu',1000);
select * from Employee_Demo
select * from Employee_Demo_Audit
