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.

7 comments:

  1. thanks vedant for this article ....i have some doubt here in above case why this query give wrong output

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

    may be something wrong here

    if u write
    update vWPersons Set Gender='Female' where Id=1

    then it make sense....that i will update wrong ......if i m wrong then ignore this..... and explain it

    ReplyDelete
    Replies
    1. Yes Correct.
      it sould be vWPersons instead of tblGender.
      well Very Nice tutorial.

      Delete
  2. Actually triggers are great to use in realtime....we use it in our production database on order table...for simplicity if we take your example then here you can put check in the trigger that if someone tries to change female person gender to male ...then u can reject that change via trigger....trigger is the closest thing to the database so if u want super tight control on the data then u should use trigger to handle the scenario..

    ReplyDelete
  3. it was a good explanation, but can you fix the error? Its confusing people. I don't see error on
    Update tblGender Set Gender='Female' where Id=1.

    Thanks any way

    ReplyDelete
    Replies
    1. yes, thts what i was wondering. its an error. Actually a better example of using triggers is quite commonly mentioned on online articles . created for keeping record of deletes on a table, by recording them in a diff table using Insert/Update/Delete triggers

      Delete
  4. In case, we make use of ‘After Trigger’, then constraints are checked prior to inserting the data into the implied table. The primary key constraint is violated in this case. Therefore, we can’t put data into virtual table. As a result, we will not find the trigger firing. On the other hand, on making use of ‘Instead of Trigger’, data is inserted into the virtual table prior to the constraint check.

    ReplyDelete
  5. Dear Sir, Please share a instead of trigger query for updation in view

    ReplyDelete

If you are aware of any other sql server questions asked in an interview, please post them below. If you find anything missing or wrong, please feel free to correct by submitting the form below.

 
Disclaimer - Terms of use - Contact Us