Trigger vs. stored Procedure

Question

I have a stored procedure that updates a flag in a table and a trigger that deletes the row updated and insert it in a new table. So the same functionality of the trigger can be added in the stored procedure. So I just wanna know:

Which is better to use: stored procedure or trigger? And in what cases? In other words, can you give me the advantages and disadvantages of each one?

Note that I'm using SQL server 2008 and I'm connecting VB.NET to my database.

Solution 1

The trigger is called automatically by your database if a special event occurs (insert, update, delete). The stored procedure is simply a user wrote database function. This function can extend the database functionality our simply group complex operations. The user or a external program is responsible to trigger the call of this extra function.

Trigger can call stored procedures.

My advice ... if you want a automated reaction to events from your database then use a trigger.

Use procedures to avoid code redundances in your database code.

Solution 2

It totally depends on your requirement,suppose you have an insert trigger & your table expects frequent inserts. then it is going to slow down the insert process. Sp on the other hand will be executed only when you are going to command.

Both as SQL objects hence no difference in the terms of execution plan etc.

But again it depends on your requirement, if you want your table to be updated in real time then go for trigger, else if you can afford to wait for an hour or two to bring the data in consistent state then go for SP.

Good luck

Comments

[User7291]

if my sp deletes and inserts a record ... and my trigger deletes and inserts a record this sp is called so is it better to merge the code of the trigger and sp in 1 SP or it's better to use seperate sp and trigger?

[User7291]

so if my sp deletes and inserts a record ... and my trigger deletes and inserts a record this sp is called so is it better to merge the code of the trigger and sp in 1 SP or it's better to use seperate sp and trigger?

[Ashutosh Arya]

It will not affect the performance in any way as i said SP and Trigger both are stored in DB hence Execution plan of both is present, if you are calling SP from trigger it's okay. But if you are planning to remove trigger from picture then you surely can gain some performance.

[OkieOth]

I would merge it. The result depends from your application design. For instance is there any chance that a second programm write to your database, it's more secure to use a trigger. A trigger can also reduce your application code. The disadvantage your programm must still trust the database that the trigger works and it makes the behavior of your program more "nebulous".