SQL Trigger Example

In this tutorial you will how to create a trigger in MS SQL Database. Trigger is a SQL Object just like stored procedure, but there are some differences, and the way object is used.

Learn Trigger in MS SQL Database

What is Trigger in SQL Database 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

    We write trigger on database

    CREATE TRIGGER trigger_name
    ON { DATABASE |  ALL SERVER}
    [WITH ddl_trigger_option]
    FOR {event_type | event_group }
    AS {sql_statement}
    

    Here is a quick example.

    CREATE TRIGGER tr1DbChanges
    ON DATABASE
    FOR	
        CREATE_INDEX,
        ALTER_INDEX, 
        DROP_INDEX
    AS
    BEGIN
        SET NOCOUNT ON;
    
        INSERT INTO tblogs (
            event_data,
            changed_by
        )
        VALUES (
            EVENTDATA(),
            USER
        );
    END;
    GO
    

    Learn more about DDL Trigger on SQL Server

  • 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, and then we check if "tbLog" table is updated with new record or not!

sql After update trigger example
SQL Trigger After Insert, After Delete

Similarly we can write for After Insert trigger and After Delete trigger in ms sql database.

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 in SQL Server Database

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

SQL 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

 
Hire SQL Developer
SQL Triggers Example
SQL Training: For any group or corporate training, please contact us at webtrainingroom(at)gmail.
SQL job Interview Questions Answers
Course in Demand
SQL database development tutorials for learning sql query, data manipulation language, working with MS SQL Server.
SQL Triggers Example
MS SQL Examples | Join MS SQL Course