Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Posts
    153

    Unanswered: Database Trigger Vs. Stored Procedure

    Hi All,

    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?

    Thanks with Advance,
    JD

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    a stored procedure could do an audit ONLY if it is called. A trigger would perform the audit no matter what the method of modifying the table.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    and your trigger may call an sp when the table is modified due to the fact you want to do some type of processing that is not possible to do within a trigger.
    Dave

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    As a rule of thumb, except for very simple triggers, a trigger should be made up of a call to a stored procedure only.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Sep 2009
    Posts
    2
    helo
    i need to use jdeveloper, can anyone help me with a site where i can go to install it?
    Thanks
    Ann

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Download section of the Oracle Technology Network.

    Next time, please, do not hijack other members' topics (especially when your question has nothing to do with the original subject) - open your own, new topic instead.

  7. #7
    Join Date
    Sep 2009
    Posts
    2
    am sorry
    its new at this

  8. #8
    Join Date
    Dec 2003
    Posts
    1,074
    Why do you say this?

    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.

    --=Chuck

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    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.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •