Menu
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.
476
Stored procedures can be invoked explicitly by the user. It's like a java program , it can take some input as a parameter then can do some processing and can return values. On the other hand, trigger is a stored procedure that runs automatically when various events happen (eg update, insert, delete).
A: Yes, we can call stored procedure inside the trigger.
Procedures doesn't return any values their just get parameters and do something with them, functions does the same by their also can return you a value based on their work. Triggers are kind of event handlers that react on any action you want and start procedure when this action happens.
Advantage of a stored procedure over a database trigger.Stored procedures can accept parameters and can return values. Triggers can neither accept parameters nor return values. A Trigger is dependent on a table and the application has no control to not fire a trigger when not needed.
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.
75
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
