Free Web Development Online Tutorials, Learn to Code
Learn how to Create SQL Trigger, After Insert trigger, After Update Trigger Example

SQL Trigger Example

In this tutorial you will how to write trigger in SQL

Create SQL Trigger

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"

SQL Trigger Types

  • 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

sql After update trigger example

SQL Trigger After Insert, After Delete

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())

SQL Instead of Trigger Example

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