SQL Server Performance Forum – Threads Archive

For the same work, Is trigger better than Sproc ?

I know everyone says trigger are slower if it does big job. Choice one: I am going to build an application. This application will do insert/update/delete always one at a time using insert/update/delete stored procedures. Those procedures after inserting/updating/deleting does some extra work say writes it to another tables and some other processing such as writing to summary table Choice two: Create a trigger for insert/update/delete and move the code for Extra work from stored procedure to Trigger. Stored procedures for insert/update/delete will still be there without the code for ‘extra work’ This choice frees me from having to use stored procedure all the time from my application for DML
This choice gives me freedom to use multi-row insert/update/delete statements as against one row at a time. In trigger i can take care in trigger if there is multi-row insert/update/delete
This is OLTP application. Lots of inserts/updates/deletes While choice two gives me freedom, i am afarid whether this will affect performance. My question is for a given "amount of extra work that has to occure for all DML", is stored procedures better or trigger better? A good insightful reply will greatly assist in my DB design Regards
Mani

---

Well what i have learnt is performance depends on the query you have written.
It does not depend on sql whether it is fired by SP or trigger.
i feel both do the process in batch so both will gonna take exact time.
So it depends on you to take thedecision whether to maintain sp or trigger. But as you said you need to incorporate the extra work within SP with some other syntax too.
So i feel you can go with trigger for that extra work.
The performance is gonna be same either that extra work is done by sp or trigger as both will complete the task in a batch.
The thing here is you dont need to call trigger what you will do for SP.

---

Why do you need such "freedom"?
Are you sure you can’t design stored procedure to do the same? As you can see I prefer stored procedure beacuse of better performance, maintenance (you have complete logic in a stored procedure instead of having part of logic in trigger) and controll (you can give users rights to specific stored procedure instead of set of tables).

---

I also go with stored procedure if you can achieve the same functionalities

---

This is a desktop application making use of MSDE. The only user of the database is teh application. Acutal Users of the application wont even know that application is making use of database. SO security, users permission do not come into play. mmarovic: you are saying " you can see I prefer stored procedure beacuse of better performance" What i want to know is why you think stored procedure provides better performance given that code for extra work is same whether it is in SP or in trigger. For example:
Choice one:
Create Procedure PriceTableUpdate( @PriceNo int, @Price money)
as
begin
insert into PriceTableHistory select * from PriceTable where [email protected]
Update PriceTable set [email protected] where where [email protected]
end Choice two:
Create trigger trigPriceUpdate on PriceTable
for update
begin
insert into PriceTableHistory select * from deleted
end Create Procedure PriceTableUpdate( @ProceNo int, @Price money)
as
begin
Update PriceTable set [email protected] where where [email protected]
end Tell me which one will run faster and why? Regards
Mani

---

I actually agree with ranjitjain. From performance point of view, it should not make any difference.

---

Regarding performance, you have scripts now, you are free to test it yourself. That’s much better then to count on opinion of random internet poster <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /><br /><br />My other point was that someone looking at your stored procedure wouldn’t know that there is additional processing in trigger. I had problems debugging stored procedures when triggers were involved, so I prefere to have all the code inside stored procedure.

---

]]>