Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2009
    Posts
    2

    Unanswered: Why create triggers if we can do everything in the Stored Procedure

    Hello People,

    I was asked this question in an interview and I could not answer.
    "Why create triggers if we can do everything in the Stored Procedure"

    Can someone give me a very simple scenario where we have to create a trigger to do something and which just cannot be done using stored procedure.

    Thanks.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Imagine you have an audit trigger to monitor record deletions.

    What happens when someone deletes frmo the table through a method other than the stored procedure? Perhaps someone has ODBC/ADO access using Access or similar?
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2009
    Posts
    2
    Thanks georgev..but what If there are no SQL's elsewhere and only stored procedures are used, can't we have the audit trail inside the SP's?

    Also in what other cases it can make sense to use Triggers?

    Thanks.

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    nav2000, the point is that with a Trigger, it will always 'fire' no matter how the table is accessed (bulk load may bypasses a trigger. I am not as familiar with them in the SQL Server environment). You may only know about stored procedures, someone 'forgets' to add the audit code (or the call to and audit process) or sometime in the future other methods than stored procedures may be used. With a Trigger you (nor the programmer) needs to worry about it.

  5. #5
    Join Date
    Sep 2009
    Posts
    39
    Quote Originally Posted by nav2000
    Hello People,

    I was asked this question in an interview and I could not answer.
    "Why create triggers if we can do everything in the Stored Procedure"

    Can someone give me a very simple scenario where we have to create a trigger to do something and which just cannot be done using stored procedure.

    Thanks.
    with triggers you can prepared all data for reports without big query which lasts few hours or days.
    then you have select from one ili two tables.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    On principle, data-oriented busieness logic should be kept as close to the data as possible. This means, in order of proximity:
    1) Datatypes
    2) Constraints
    3) Triggers
    4) Procedures and Views
    5) Client-side code
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Triggers can be used to enforce business rules that can't be enforced by a constraint. In that sense triggers are the next best thing to a constraint because as Blindman suggests they are closest to the data and less likely to be bypassed by other logic. True, triggers can be bypassed but then so can foreign key and check constraints.

    In my experience it's best to avoid triggers for any other logic, especially any logic that modifies data. A trigger is just a procedure that is triggered by some other code. In most cases it makes sense to modify that other code rather than use a trigger. That's because triggers almost inevitably obfuscate code to some degree plus they are hard work to maintain - especially for DBAs doing data maintenance tasks.

Posting Permissions

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