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.

more ▼

asked Jan 08 '10 at 10:24 AM in Default

OracleApprender gravatar image

771 68 73 75

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

more ▼

answered Jan 11 '10 at 11:42 AM

OracleApprender gravatar image

771 68 73 75

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jan 08 '10 at 10:44 AM

HillbillyToad gravatar image

1k 2

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.
Jan 08 '10 at 11:14 AM OracleApprender
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?
Jan 08 '10 at 01:17 PM HillbillyToad
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Feb 01 '10 at 09:21 AM

Lars gravatar image


(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 08 '10 at 10:24 AM

Seen: 1775 times

Last Updated: Jan 08 '10 at 10:24 AM