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.
 |
thanks vedant for this article ....i have some doubt here in above case why this query give wrong output
ReplyDeleteUpdate 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
Yes Correct.
Deleteit sould be vWPersons instead of tblGender.
well Very Nice tutorial.
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..
ReplyDeleteit was a good explanation, but can you fix the error? Its confusing people. I don't see error on
ReplyDeleteUpdate tblGender Set Gender='Female' where Id=1.
Thanks any way
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
DeleteIn 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.
ReplyDeleteDear Sir, Please share a instead of trigger query for updation in view
ReplyDelete