What are the advantages of a stored procedure over a database trigger ?
Can anybody explain me the advantages of a stored procedures over a database triggers. Thanks in advance.
Best Answer
I got answer from the google as below.
We have control over the firing of a stored procedure but we have no control over the firing of a trigger.
They serve two different purposes. A procedure executes only when called. A trigger is 'triggered' by a system event and allows to intercede on INSERTS/UPDATES/ALTERS and more.
You can write a trigger that calls a procedure. You can't really write a procedure that 'calls' a trigger.
What are you looking to achieve? The scenario will determine the best method. There's no rule that triggers are 'better' than procs and vice versa.
I want to know any specific advantage of a Stored Procedure over a databae trigger. If there is nothing like that then I feel no need of having these two different things for the similar purpose.
One is a program that you run to accomplish a task. The other is something that fires when a certain condition is met automatically. One is a hammer. One is a screwdriver. You could use the screwdriver to hammer a nail, but why would you?
Strange answer in my opinion. The trigger fires when it supposed to fire; when at table gets a new row (just one of many examples). So that is very much in control. And it will always fire.
But HillybillyToad has a very good point, what are you trying to acheive? If you need a hammer for your 1 inch screw it will do no good.
Usually in the applications I have seen both, either a strict programming rule book that covers what to do on each object (consisting of many tables, or just one) or some trigger happy people. If you like triggers then use them in order to make data team up so to speak. There will be no errors in the database if you apply triggers for columns that need to be there, sequence numbers and such things.
And yes, the google answere is of course correct. You, as a developer has more control over when a stored procedure is fired. But the control of the firing trigger is in the control of the designer - or developer - of the application as a whole. And again, it will always fire. That is what I really call control.
