web training room for online courses
Learn SQL Database Development, MS SQL Tutorial Online

How to write Triggers in SQL Server Example

What is Trigger in SQL Databse and when we do we use them?
"A trigger is a special kind of Stored Procedure or stored program that is automatically fired or executed when some event (insert, delete and update) occurs on any Table"

Types of Triggers
  • Data Definition Language (DDL) triggers
  • Data Manipulation Language (DML) triggers
    • After trigger (using FOR/AFTER CLAUSE)
    • Instead of Trigger (using INSTEAD OF CLAUSE)
  • Logon triggers

Trigger in SQL server example

DDL Triggers : This type of trigger is fired against DDL statements like Drop Table, Create Table or Alter Table. DDL Triggers are always After Triggers.

DML Triggers : Let’s write a After Update trigger, whenever any record updated on tbStudent table, we want to track the details into tbLog table, so we have written the below trigger

create TRIGGER triStuUpdate on tbStudent
FOR Update               
    AS declare @stuid varchar,  @subject varchar(50), @message nvarchar(max);                
    select @stuid=i.StudentId from inserted i;
    select @subject=  'tbStudent Updated';
    select @message='StudentId =' + @stuid;                
INSERT INTO [tbLog]                
    ([LogSubject],[LogMessage],[LogDate])
VALUES
    (@subject,@message,getdate())

Now trigger is created, let's test it, simply update a record on tbStudent table

After update trigger test result

Similarly we can write for After Insert trigger and After Delete trigger

create TRIGGER triStuInsert on tbStudent
FOR Insert
    AS declare @stuid varchar,  @subject varchar(50), @message nvarchar(max);
    select @stuid=i.StudentId from inserted i;
    select @subject=  'tbStudent Inserted';
    select @message='StudentId =' + @stuid;                
INSERT INTO [tbLog]                
    ([LogSubject],[LogMessage],[LogDate])
VALUES
    (@subject,@message,getdate())

Instead of Trigger (using INSTEAD OF CLAUSE)

create TRIGGER triStuInsteadUpdate on tbStudent            
Instead of Update              
    AS declare @stuid varchar,  @subject varchar(50), @message nvarchar(max);             
    select @stuid=i.StudentId from inserted i;             
    select @subject=  'tbStudent Instead';             
    select @message='tbStudent Instead of Updated: StudentId =' + @stuid;             
INSERT INTO [tbLog]              
    ([LogSubject],[LogMessage],[LogDate])             
VALUES             
    (@subject,@message,getdate())

The Instead of fires before SQL Server starts the execution of the action that fired it. The different from the AFTER trigger that fires after the event, instead of fires before event, that means if Instead of trigger executed successfully does not include actual event.

Logon Triggers

Logon triggers are a type of triggers that fire when a LOGON event of SQL Server is occurred.

Nested Trigger

What is nested trigger?
when a trigger performs an action that initiates another trigger, is called nested trigger, DML and DDL triggers can be nested up to 32 levels


Comment
Name Email Website
Subscribe
 
Triggers in SQL Database

SQL Triggers Example
Consulting