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

1

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:

1

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:

2

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.

3

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.

4

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.

5

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.

6

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

8

  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:

9

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

10

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

11

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;

12

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:

13

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.

1

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.

2

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

1

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

2

Disabling or Enable the DDL Trigger

If we want to disable trigger we need to run the below shown command:

3

System stored that performs DDL triggers

Rename

Renaming the table name or column name



4

5

Views in SQL Server

A View is a saved SQL query which act as a Virtual table. View is generally used for security mechanism by allowing user to access the data through View rather than granting the other user to directly access the db.

Syntax to create a View

CREATE VIEW vwViewName
as
Select Query
----
---

We have two table tblperson and tblGender, i want to create a view which shows all the person records along with it corresponding Gender as shown below:

1

2

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

Retrieving data from the View

select * from vwPerson

Updateable View

One of my friend asked my today that can we update records uusing a View and th3e answer is Yes. View can be used to update,insert or delete the records in underline table used in View. Internally all the statements are fired against the base table present in View. In order to demonstrate that View can be used to insert, update and delete records from the base table, lets take above mentioned view and try to update the records on the same.

I want to update email address of Saillesh. So in order to do the same i will trigger the update query as mentioned below:

  update [dbo].[vwPerson] set Email='shaillesh.pawar@microsoft.com' where Name='Saillesh'

Once we run the query, we will se output 1 row affected. Now in order to check whether the corresponding base table has been updated or not? lets query the person table.

4

When we try to update the column which is basically a join of two columns the update command is not executed properly resulting in incorrect behavior. If i want to update gender of Nishant to Male. I will execute the below query.

  update [dbo].[vwPerson] set Gender='Male' where Name='Nishant'

But once the query is executed we will see the below output, which seems perfect but when we dig deep we will find that the update command has been run over child table which will result in inappropriate results when we have others records with Gender as “Female”

6

Below we will see rather than updating the corresponding ID for the gender in parent table it updated the Gender of child table to “Male” which will result in appropriate data.

7

In order to handle these kind of update issues we make use of Triggers.

Indexed View

Indexed View is stored sql query similar to stored procedure which is stored in cache. By default View doesn’t store any data on it’s own. But if we create a index on View it gets Materialized, which enables view to store data.

In order to deomonstrate the same lets take an example of two tables Order and Product table as mentioned below:

1

Records present in both the tables are shown below:

2

Lets create a view which returns the total sale and number of each product which has been sold.

Steps for creating Indexed Views or Materialized View

  • View should be created with SchemaBinding option.
  • If there is a expression in select list which value can be null than alternate replacement value should be defined.
  • If Group by is specified use Count_big(*) returns bigInt
  • Base table to be referred as two part name schemaName and tableName as shown below in figure:
  • First Index to be create on Index View has to be unique clustered Index

8

Creating an Index on the View

Now if i try use count(*) instead of count_big(*) sql server will throw me error to use count_big(*) instead of count(*), when we try create Index on View as shown below:

9

Changing count(1) to count_big(*))

alter view VwTotalOrderByProduct
with SchemaBinding
as
select [Description],
SUM(isnull((Quantity * price),0)) as Sales,
count_big(*) totalCount
  from
  [dbo].[mstOrderDetails] orders inner join
  [dbo].[Product] prod on orders.productid=prod.[ID]
  group by prod.[Description] 

Creating Index on View

10

Now whenever we execute the View it will retrieve the records from the Index rather than going to the actual base table. If any row is inserted the View is also again computed again. Indexed Views are basically used where records are rarely changed.

Limitation of Views

    • Order by clause is not allowed in View

Order by clause should be used outside the View not inside the view. If we try to use Order by clause inside View it will threw an error as shown below:

3

But the error message say we can use Order by clause if we use Top, Offset, For xml. Lets try the same.

3

But when we tried executing the View the output doesn’t seems to follow the order by clause.

select * from [dbo].[vw_ViewPersons]

When we try to execute above mention query, sql server will perform percent on 100% on 3005 records which after round of returns 3005 records. Now lets execute the above query.

5

We can see the that returned records are not in Order by clause. Lets see the SQL plan for the same.

7

We can easily conclude that the it doesn’t perform the sorting for the same. While if we only show rows less than the number of rows present in table than Order by clause. We will change our View as per below logic.
USE [master]
GO
/****** Object: View [dbo].[vw_ViewPersons] Script Date: 12-Jul-17 11:58:46 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

alter view [dbo].[vw_ViewPersons]
as
SELECT TOP 10 percent [PERSONID]
,[FIRSTNAME]
,[MOTHERID]
FROM [SampleDb].[dbo].[Person]
order by MOTHERID desc

GO

 

Now this view will return 301 records with Order by Clause.

8

  • We cannot create parameterized view
  • We cannot create parameterized view instead we can use where clause on View. In order to create a parameterized view we can make use inline table valued function which takes in a parameter and returns a table.

  • View cannot be created on Temporary table
  • Column issue in View
  • Once the view is created and after sometime we add a new column to the table. It’s not reflected in View till is refreshed.

    
    USE [SampleDb]
    GO
    
    /****** Object:  View [dbo].[vw_ViewPersons]    Script Date: 13-Jul-17 10:10:18 PM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER view [dbo].[vw_ViewPersons]
    
    as
    SELECT TOP 100 percent *
      FROM [dbo].[Person]
      order by MOTHERID desc
    
    GO
    
    

    Now we will add one more column to the table Person.

    
    alter table [dbo].[Person]
    add lastName nvarchar(40)

    Now let’s try to run the View and see whether the new column is reflected or not?

    9

    Hence we can conclude that if we add new Column it will not be reflected to View. Let try to refresh the View and than try.

    
    SP_REFRESHVIEW [vw_ViewPersons]

    9

    Same drawbacks remains when we delete any column from the table

    
    alter table [Person]
    drop column lastname

    Now if we try to run our View we will see the following output

    11

    After refresh

    12