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.

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"

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

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