I have a doubt regarding the actual implementation of trigger(DML) over the Stored Procedure(SP). I would like to know when a specific task (say audit trail) can be performed by using triger as well as using SP also, then why should we go for trigger? Could you please give some example where we have to use trigger and the same can not be performed by SP?
As a rule of thumb, except for very simple triggers, a trigger should be made up of a call to a stored procedure only.
Just a curiosity...
We're looking to alter the way we do things (all business rules are currently in database triggers), or at least re-evalute how we're doing things, as we move to a .NET front-end, Oracle back-end. These types of comments, and their reasoning, are very helpful to us right now.
if the table is busy, a trigger may not be replaced. However if you have a package procedure being called by the trigger, that can be modified at will and simply compile the new body to change the function of the trigger.
You do not need a parachute to skydive. You only need a parachute to skydive twice.