SQL Server Interview Questions on triggers

What is a Trigger in SQL Server?
A Trigger is a database object that is attached to a table. In many aspects it is similar to a stored procedure. As a matter of fact, triggers are often referred to as a "special kind of stored procedure." The main difference between a trigger and a stored procedure is that the former is attached to a table and is only fired when an INSERT, UPDATE or DELETE occurs.

What are the two types of Triggers in SQL Server?
1. After Triggers : Fired after Insert, Update and Delete operations on a table.
2. Instead of Triggers: Fired instead of Insert, Update and Delete operations on a table.

What are the special tables used by Triggers in SQL Server?
Triggers make use of two special tables called inserted and deleted. The inserted table contains the data referenced in an INSERT before it is actually committed to the database. The deleted table contains the data in the underlying table referenced in a DELETE before it is actually removed from the database. When an UPDATE is issued both tables are used. More specifically, the new data referenced in the UPDATE statement is contained in inserted table and the data that is being updated is contained in deleted table.

Give a real time example for triggers usage?
It is recomended to avoid triggers in a real time environment. There is one scenario I can think of why you may want to use triggers in a real time environment. Let us use an example to understand this.



I have 2 tables, tblPerson and tblGender as shown below. GenderId is the foriegn key in tblPerson table.

Now create a view based on the above 2 tables as shown below.

Select * from vWPersons will give us the result as shown below.

Now update the view the following query. This will change the Gender Text to Female in tblGender table for Id = 1. This is not what we have expected.

Update tblGender Set Gender='Female' where Id=1

The base tables are updated incorrectly. So, Select * from vWPersons will give us the result as shown below.

To update the base tables correctly, you can create an INSTEAD OF trigger on the view as shown below.

Now run the query below which will update the underlying base tables correctly.
Update vWPersons Set Gender='Female' Where Id=1

Select * from vWPersons, will show the correct result set as shown below. The INSTEAD OF trigger has correctly updated the GenderId in tblPerson table.
So, Instead of triggers can be used to facilitate updating Views that are based on multiple base tables.