Description:
A SQL Server trigger is a piece of procedural code, like a stored procedure which is only executed when a given event happens. There are different types of events that can fire a trigger. Just to name you a few, the insertion of rows in a table, a change in a table structure and even a user logging into a SQL Server instance.
There are three main characteristics that make triggers different than stored procedures:
- Triggers cannot be manually executed by the user.
- There is no chance for triggers to receive parameters.
- You cannot commit or rollback a transaction inside a trigger.
The fact that it's impossible to use parameters on triggers is not a limitation to receive information from the firing event. As you will see further on, there are alternatives to obtain information about the firing event.
There are two classes of triggers in SQL Server:
- DDL (Data Definition Language) triggers. This class of triggers fires upon events that change the structure (like creating, modifying or dropping a table), or in certain server related events like security changes or statistics update events.
- DML (Data Modification Language) triggers. This is the most used class of triggers. In this case the firing event is a data modification statement; it could be an insert, update or delete statement either on a table or a view.
Additionally, DML triggers have different types:
- FOR or AFTER [INSERT, UPDATE, DELETE]: These types of triggers are executed after the firing statement ends (either an insert, update or delete).
- INSTEAD OF [INSERT, UPDATE, DELETE]: Contrary to the FOR (AFTER) type, the INSTEAD OF triggers executes instead of the firing statement. In other words, this type of trigger replaces the firing statement. This is very useful in cases where you need to have cross database referential integrity.
One of the fundamental characteristics of relational databases is data consistency. This means that the information stored in the database must be consistent at all times for every session and every transaction. The way relational database engines like SQL Server implement this is by enforcing constraints like primary keys and foreign keys. But sometimes that is not enough.
In SQL Server there is no chance to enforce referential integrity between two tables using foreign keys if those tables are in different databases or different servers. In such case the only way you can implement it is by using triggers.
Example in MS SQL :
SQL Server DML Trigger Syntax
In the next code section, you will see the basic CREATE TRIGGER syntax.
CREATE TRIGGER trigger_name ON { Table name or view name } [ WITH <Options> ] { FOR | AFTER | INSTEAD OF } { [INSERT
], [UPDATE] , [DELETE] }
Create database Demotrigger
Create table dbo.employee(
id int identity(1, 1),
name varchar(50),
city varchar(50),
department varchar(50),
loginname varchar(50),
modifieddate datetime,
action char(1),
)
Create TRIGGER TR_Employees ON dbo.employee
FOR INSERT, UPDATE, DELETE
AS
DECLARE @login_name VARCHAR(128)
SELECT @login_name = login_name
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID
select @login_name
IF EXISTS ( SELECT 0 FROM Deleted )
BEGIN
IF EXISTS ( SELECT 0 FROM Inserted )
BEGIN
INSERT INTO dbo.employee
( name ,
city ,
department,
loginname,
modifieddate,
action
)
SELECT D.name ,
D.city ,
D.department ,
@login_name ,
GETDATE() ,
'U'
FROM Deleted D
END
ELSE
BEGIN
INSERT INTO dbo.employee
( name ,
city ,
department,
loginname,
modifieddate,
action
)
SELECT D.name ,
D.city ,
D.department ,
@login_name ,
GETDATE() ,
'D'
FROM Deleted D
END
END
ELSE
BEGIN
INSERT INTO dbo.employee
( name ,
city ,
department,
loginname,
modifieddate,
action
)
SELECT I.name ,
I.city ,
I.department,
@login_name ,
GETDATE() ,
'I'
FROM Inserted I
END
GO
select * from dbo.employee
insert into employee values('Raj Kumar','Vanarash','IT','',getdate(),'I')
Update employee set department='computer' where id=2