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
-
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!
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())
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 are a type of triggers that fire when a LOGON event of SQL Server is occurred.
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
