Triggers in SQL

Trigger is a kind of pre or post processive logic which fires automatically when DML(Insert, Update and Delete) or DDL() commands are fired against the table. Trigger has basically used to intercept the action being performed on the table.

DML Trigger can be classified into 2 types:

Instead of Triggers (for triggers)
After Triggers

Instead Of Triggers
Instead of Triggers fires instead of the triggering action i.e. trigger which gets fired before DML operation.

After Triggers (For Triggers)
After Trigger fires the post processive logic after the respective DML statment completes execution (insert, update and delete).

After Triggers

We have table named GST which contains the GSTIN number. Consider that based upon GSTIN number the organization will bill the customer on his/her shipment. Following are the account number with respect to their state as shown below:

As these GST details are important to the organization. So each time any DML operation are performed we want to audit these details to the GSTAudit table. So we want the account number with GSTIN number and the datetime at which it was added or updated or deleted. Many times we have acheived these kinds of scenarios using stored procedure but the easiest way to do this is by using Triggers. So now as soon as a DML query is fired on the table our After trigger will get executed and insert the details into GSTAuditTable.

Syntax

create trigger tr_triggerName
on GST
FOR INSERT 
AS
BEGIN
INSERT INTO TABLE
END

We create a trigger for a specific table and event so we have to mention the same as shown in above code snippet where we are creating a trigger on Insert event.

GSTAudit table script

CREATE TABLE [dbo].[GSTAudit](
	[Id] [int] NOT NULL identity(1,1),
	[AuditInformation] [nvarchar](200) NULL
) ON [PRIMARY]
GO

Trigger on GST table on Insert

create trigger tr_tblGst_ForInsert
on GST
FOR INSERT 
AS
BEGIN
Declare @Id int
Declare @gstn nvarchar(15)
select @id=GSTID,@gstn=GSTIN from inserted
INSERT INTO GSTAudit values('The New GST number'+@gstn +'with Id='+cast( @id as nvarchar(10)) +
                             'added at '+cast(getdate() as nvarchar(20)))
END

Now when i insert a new records into GST table we will get the Audit Information into Audit table as shown below:

You may be wondering from where does inserted table came from in the trigger?
inserted table is a special kind of table which is available only in the context trigger. When we insert the records into the table the inserted table saves the records which were inserted into the table in memory to be used as per the user logic. The structure of inserted table is identical to structure of table in which trigger is maintained.

Now we want to create a trigger on the GST table, whenever a delete operation is performed on the table, we want to store the user information and the gstId that has been deleted.

USE [master]
GO
/****** Object:  Trigger [dbo].[tr_triggerName]    Script Date: 25-Jul-17 11:18:32 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter trigger [dbo].tr_tblGst_ForDelete
on [dbo].[GST]
FOR delete 
AS
BEGIN
Declare @Id int
Declare @gstn nvarchar(15)
select @id=GSTID,@gstn=GSTIN from deleted
INSERT INTO GSTAudit values('The GST number'+@gstn +'with Id='+cast( @id as nvarchar(10)) +
                             'deleted on '+cast(getdate() as nvarchar(20)) +' by '+CURRENT_USER)
END
GO

Now the way inserted table is used to stored new inserted record likewise deleted table is used to store deleted row as shown above.

Firing the trigger when a row is updated. As we know the Insert trigger make use inserted table while for delete we make use of deleted table in order to fetch our information related to inserted or deleted row. In update trigger we have both the tables i.e. inserted and deleted. The inserted table contains the updated records i.e. new records while deleted table contains the old record. So now we will creating a small trigger which will fire when only one rows is updated.


USE [master]
GO

/****** Object:  Trigger [dbo].[tr_tblGst_ForUpdate]    Script Date: 26-Jul-17 9:56:35 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




ALTER trigger [dbo].[tr_tblGst_ForUpdate]
on [dbo].[GST]
FOR Update
AS
BEGIN
Declare @id int, @OldAccountNo nvarchar(20), @OldGstn nvarchar(15), @OldSTATEID int,@OldCITYID int
Declare @NewAccountNo nvarchar(20), @NewGstn nvarchar(15), @NewSTATEID int,@NewCITYID int
Declare @AuditInformation nvarchar(500)

select @id=GSTID,@OldGstn=GSTIN, @OldAccountNo=ACCOUNTNUMBER,@OldSTATEID=STATEID,@OldCITYID=CITYID from deleted
select @NewGstn=GSTIN, @NewAccountNo=ACCOUNTNUMBER,@NewSTATEID=STATEID,@NewCITYID=CITYID from inserted
 
set @AuditInformation='GSTN Id ='+CAST (@id as nvarchar(150))+'updated '

if(@OldGstn@NewGstn)
set @AuditInformation=@AuditInformation+' GST Number from '+@OldGstn + ' to '+@NewGstn


if(@OldAccountNo@NewAccountNo)
set @AuditInformation=@AuditInformation+' AccountNumber Number from '+@OldAccountNo + ' to '+@NewAccountNo

if(@OldSTATEID@NewSTATEID)
set @AuditInformation=@AuditInformation+' stateid from '+CAST( @OldSTATEID as nvarchar(2)) + ' to '+cast (@NewSTATEID as nvarchar(2))

if(@OldCITYID@NewCITYID)
set @AuditInformation=@AuditInformation+' stateid from '+CAST( @OldCITYID as nvarchar(2)) + ' to '+cast (@NewCITYID as nvarchar(2))

 INSERT INTO GSTAudit values(@AuditInformation)
END
GO

Here we create the old and new variable to validate whether they have been changed or not. If yes than use the same for audit. We can see how we are making use of both deleted and inserted table.

Now let’s update one records and see the results in audit table.

If in case the update command will update more than one records our logic will get fail because at that time there will be more than one records in inserted and deleted table as shown below.



ALTER trigger [dbo].[tr_tblGst_ForUpdate]
on [dbo].[GST]
FOR Update
AS
BEGIN

select * from deleted
select * from inserted
END
GO

Lets execute a update command on the table.

So now in order to handle the multiple updated records we need to create a while loop and verify the new records in inserted table with old records and print the changes.

ALTER trigger [dbo].[tr_tblGst_ForUpdate]
on [dbo].[GST]
FOR Update
AS
BEGIN
Declare @id int, @OldAccountNo nvarchar(20), @OldGstn nvarchar(15), @OldSTATEID int,@OldCITYID int
Declare @NewAccountNo nvarchar(20), @NewGstn nvarchar(15), @NewSTATEID int,@NewCITYID int
Declare @AuditInformation nvarchar(500
select * into #tempGst
from inserted

while(exists(select gstid from #tempGst))
begin
select top 1 @id =gstid,@NewAccountNo=ACCOUNTNUMBER,@NewGstn=GSTIN,@NewSTATEID=STATEID,@NewCITYID=CITYID
from #tempGst

select @id=GSTID,@OldGstn=GSTIN, @OldAccountNo=ACCOUNTNUMBER,@OldSTATEID=STATEID,@OldCITYID=CITYID from deleted where GSTID=@id
set @AuditInformation='GSTN Id ='+CAST (@id as nvarchar(150))+'updated '
if(@OldGstn@NewGstn)
set @AuditInformation=@AuditInformation+' GST Number from '+@OldGstn + ' to '+@NewGstn
if(@OldAccountNo@NewAccountNo)
set @AuditInformation=@AuditInformation+' AccountNumber Number from '+@OldAccountNo + ' to '+@NewAccountNo

if(@OldSTATEID@NewSTATEID)
set @AuditInformation=@AuditInformation+' stateid from '+CAST( @OldSTATEID as nvarchar(2)) + ' to '+cast (@NewSTATEID as nvarchar(2))
if(@OldCITYID@NewCITYID)
set @AuditInformation=@AuditInformation+' stateid from '+CAST( @OldCITYID as nvarchar(2)) + ' to '+cast (@NewCITYID as nvarchar(2))
 INSERT INTO GSTAudit values(@AuditInformation)
 delete from #tempGst where GSTID=@id
end
END
GO

Here we have make use of temptable because in order to remove the records from the tempTable. So that we don’t go into infinite loop. If we try to delete the special table inserted and deleted. SQL server won’t allow us and we will get a compile time error named: The logical tables INSERTED and DELETED cannot be updated. So in order to achieve the same we make use of tempTable and than get the respective GSTId records from the deleted table and than verify the same. Than we cross verify the old and new values and insert into gstAudit table.

Instead of Trigger

Instead of Triggers are fired before any DML operation is executed. The major scenarios where we can make use of Instead of Trigger inserting records into table with a view, Creating the autogenerate sequence value to be used in the table etc.

So here we have view named vwPerson which we created during View article which retrieve records from two table

  alter VIEW [dbo].[vwPerson]
  as
  select 
  [TBLPERSON].ID,
  [TBLPERSON].[Name]
      ,[TBLPERSON].[Email]
      ,tblgender.Gender
	 
  FROM [TBLPERSON]
  inner join tblgender on [TBLPERSON].[GenderId]=tblgender.id

Now i want to insert into a new records into View. So when i insert the records into the table i get an error as shown below:

What’s really happens as we are retrieving person id and other respective columns. When we try to insert id and others values into the View. Sql Server gets confused where to insert which value. So SQL Server gives the error the modification affects the multiple base tables. So now in order to correct the same lets make use of Instead of Insert trigger. So here we are trying to insert the Person records into person table.

create trigger tr_VwPerson_InsteadOfTrigger
on vwPerson
Instead of insert
as
begin

Declare @Id int
select @Id=tblGender.Id from tblGender 
inner join 
inserted on tblGender.Gender=inserted.Gender

if(@Id is null)
begin

Raiserror('Invalid Gender Id',16,1)
return
end

insert into TBLPERSON (Name,Email,GenderId)
select name,email,@Id from inserted


end

So now we will make use of inserted table which is available in trigger. So once the user pass the insert query to view. we first check whether the foreign key table gender has that inserted gender or not? If yes than retrieve the id from the table and insert the same in tblPerson table else generate the error of Invalid Gender Id.

Performing insert on View with Instead of Trigger

Let’s try to insert a invalid Gender into the table and check whether our logic is working or not?

Instead of Update trigger

So here we will be making use of Instead of Update trigger in order to update the View. So if i try to update the view

update [dbo].[vwPerson] set Name='Sam', Email='Sam@mic.com',Gender='Female' where Id=1;

So this statement is affecting multiple base table. So in order to handle the same let’s create a Instead of Update trigger to update the correct rows. So here we want to update the name, email and genderId to 2 in TblPerson table because 2 in tblGender is Female.

USE [SampleDb]
GO

/****** Object:  Trigger [dbo].[tr_VwPerson_InsteadOfTrigger]    Script Date: 27-Jul-17 11:15:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[tr_VwPerson_InsteadOfUpdateTrigger]
on [dbo].[vwPerson]
Instead of Update
as
begin

	if(UPDATE(Id))
	Begin
		Raiserror('Id cannot be updated',16,1)
	end

	if(update(name))
	begin

	update TBLPERSON set Name=inserted.Name
		from inserted
		inner join TBLPERSON on inserted.ID=TBLPERSON.ID

	end


	if(update(Email))
	begin

	update TBLPERSON set Email=inserted.Email
		from inserted
		inner join TBLPERSON on inserted.ID=TBLPERSON.ID
	end

	if(update(Gender))
	begin

		declare @GenderId int
		select @GenderId=tblGender.Id from tblGender
		inner join inserted
		on tblGender.Gender=inserted.Gender

		if(@GenderId is null)
		begin
		raiserror('Invalid gender Name',16,1)
		RETURN
		end

		update TBLPERSON set GenderId=@GenderId
		from inserted
		inner join TBLPERSON on inserted.ID=TBLPERSON.ID
	end
end
GO

As per the trigger we are using Update function which tells us whether we are updating the mentioned column or not of the view. So the update() method will return true or false.

If someone wants to update the Id of the TblPerson we are not allowing them to update the same.

While when an Name or Email are updated of tblPerson table we are directly referring the Name and Email based on the Id.

When we want to update the Gender to Female. i.e. updating genderId to 2 to tblPerson table for id passed. so implemented the below logic.

	if(update(Gender))
	begin

		declare @GenderId int
		select @GenderId=tblGender.Id from tblGender
		inner join inserted
		on tblGender.Gender=inserted.Gender

		if(@GenderId is null)
		begin
		raiserror('Invalid gender Name',16,1)
		RETURN
		end

		update TBLPERSON set GenderId=@GenderId
		from inserted
		inner join TBLPERSON on inserted.ID=TBLPERSON.ID

	end

It first check whether Gender field is updated or not? If yes than get the GenderId of the passed Gender which is present in inserted table. If GenderId received is not null than update the gender Id to tblPerson table as shown below:

Instead of Delete Trigger

As we discusses the Instead of Insert,Update Trigger for Inserting and Updating the rows in the View. Lets discuss Instead of Delete Trigger to Delete the rows from the View.

If we try to delete the row by executing delete statement over view we will get an error that view is not updatable because the modification affects multiple base tables.

So now i want to delete the records from the view where id=6.


delete from [SampleDb].[dbo].[vwPerson] where id=6


create trigger [dbo].[tr_VwPerson_DeleteOfTrigger]
on [dbo].[vwPerson]
Instead of delete
as
begin

	delete TBLPERSON from TBLPERSON join
	deleted on TBLPERSON.id=deleted.id
end

Now the deleted table will contain the rows deleted which will than be deleted from TblPerson table.

DDL trigger

DDL triggers fires when we perform a DDL events i.e. Create, Alter, Drop, Grant, Deny, Revoke or Update Statstics.

DDL trigger are executed when we execute a DDL command like Creating a table, Altering the table etc.Whenever perform the DDL operation the linked event for the operation is raised.

Use of DDL trigger

If we want to execute some specific code when an DDL event is fired example: Alerting user that he/she can’t drop table from database.
Prevent changes in database schema.
Records changes in schema.

Syntax


CREATE TRIGGER [Trigger_Name]
on [Scope (server|Database)]
FOR [Event]
AS
BEGIN
--statements
--statements
end

For example if we create a new table than CREATE_TABLE is invoked as shown below:


CREATE TRIGGER Tr_Create
on DATABASE
FOR CREATE_TABLE
AS
BEGIN
PRINT('New table created')
end

What if want to use the same trigger for other events like alter and drop event.


create TRIGGER Tr_Event
on database 
FOR CREATE_TABLE,ALTER_TABLE,DROP_tABLE
AS
BEGIN
PRINT('You have just performed Create, Alter or Dropped table')
end