A
Stored Procedure[
^] does some stuff (like select, update, delete, make calculations) when you call it and
Trigger[
^] does some stuff (usually updates or inserts) whenever it is automatically triggered.
So assume you have a
Table with three fields
ID,
Name and
Counter. Now EVERYTIME
Name is updated you want to increment
Counter by one. You could write a
Stored Procedure for this, but for an update you would always need to call the
Stored Procedure. Someone WILL not do this though and your
Counter is now running behind. If you write a
Trigger for this
Counter is ALWAYS updated (wether you change
Name from code, a
Stored Procedure or even manually!
Of course the use of
Triggers has its downsides. I always find it to be a 'black box', I do some update statement and the result is different then I expected! After hours of debugging I find a
Trigger that alters my data. Also, if you're not careful with
Triggers they could trigger around endlessly (and cause and
Exception). In the above example what if your trigger accidentally watched the entire row?
Name will be updated then
Counter.
Counter again triggers the
Trigger which will update
Counter etc... :)
I recently had an experience with
Triggers that locked entire
Database Tables!
Also, any update that triggers a
Trigger will, of course, take a little longer because the
Trigger has to execute.
I am not saying
Triggers are bad, but you have to know when and how to use them. Best use them with extreme caution! So this is not a question of 'which is best', but what do you need.
You have to decide which approach your specific case needs.
Good luck :)
Submit an article or tip