Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2011
    Posts
    2

    Unanswered: Use of Trigger on table which can have 100 hits / second. Will it hamper performance?

    Hi All.
    Currently in one of our project we have a situation.
    The server computer is Windows Server 2008 Standard SP2. Processor is Xeon (R) 2.13 Ghz. SQL server 2008 R2.
    We need to implement database Trigger on a table which could have around 100 hits (for fetch and DML) per second. Trigger will work on Insert, Update and Delete. Wanted to know will the system performance go considerably due these triggers?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The short answer boils down to: Yes, any trigger executing 100+ times per second will have some impact on your server. Depending on the actual work done by the trigger and the server performance, your server may not be able to execute the trigger 100 times per second.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2013
    Posts
    353
    Provided Answers: 1

    A better question ..

    We need to implement database Trigger on a table which could have around 100 hits (for fetch and DML) per second. Trigger will work on Insert, Update and Delete. Wanted to know will the system performance go considerably due these triggers?
    A better question is how can I get rid of the trigger and use declarative constraints maintain data integrity.

    I am also kinda curious about a trigger that works on all database events.If you are using this for an audit. It is not a good idea! Talk to an account or an attorney; you cannot have the code that does the audit in the same table that your auditing. It is illegal, as well as a very bad practice. Just think what happens when the table is damaged. Yes, that is right your audit trail is destroyed along with the source data. This is also why you do not put temp DB and the log files on the same disk is the database.

    If this is an audit problem, then buy an audit tool. Remember in today's world ROI means "risk of incarceration"

Tags for this Thread

Posting Permissions

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