Triggers in MS SQL

 

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.

Classes of MS SQL Triggers

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.

Importance of MS SQL Triggers?

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