About trigger | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

About trigger

hi i m mahadeo,
i am trying to write a trigger on table stud(rollid,fees)
my trigger is 1.create trigger my_trigger
on stud
for insert
as
insert into stud values(’14’,1200.23) and now i m writing a insert query
insert(‘s00001’,23.45)
then trigger will be fired but when you are consider the conditon then it should be in dead lock
but it works why?
what happlen when i write trigger like 1.create trigger my_trigger
on stud
After insert
as
insert into stud values(’14’,1200.23) 2.create trigger my_trigger
on stud
INSTEAD of insert
as
insert into stud values(’14’,1200.23) is there any problem of dead lock?
if not then why? for all conditions
pls tell me.

Read this site and BOL about deadlocks. Usually it takes two processes to get a deadlock. Both are waiting for the resource held by the other process. In your case, the INSERT takes place and *then* the trigger. Why should it deadlock? Is this a homework question? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
Why would it be in deadlock? Are you expecting the INSERT trigger to continue firing? I think it will fire just once. For instance, we have a trigger for insert and update, which will run an UPDATE statement on the same rows on the same table in order to set a LastUpdate column on the rows that are already in inserted. You can create a test procedure that will do the insert on the table, then you debug the test procedure. You will see the trigger code executing during the debug. In our case, the trigger for insert and update comes up twice for an insert action, and just once for an update action. ……… uh, come to think of it: this trigger might come up twice for an update action, can’t remember exactly.
Read this to know about deadlocks
http://sql-server-performance.com/deadlocks.asp Madhivanan Failing to plan is Planning to fail
]]>