question

OracleApprender avatar image
OracleApprender asked

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.

plsqlprocedure
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

OracleApprender avatar image
OracleApprender answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

HillbillyToad avatar image
HillbillyToad answered

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.

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

OracleApprender avatar image OracleApprender commented ·
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.
0 Likes 0 ·
HillbillyToad avatar image HillbillyToad commented ·
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?
0 Likes 0 ·
Lars avatar image
Lars answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.