Professional Web Applications Themes

How widespread is the use of triggers these days and how concerned about performance?.... - Microsoft SQL / MS SQL Server

Fifteen years ago when databases were primarily used in the context of transaction-intensive applications, triggers were a nice feature of a DBMS system that did not get used because of performance concerns. Fast forward 15 years, it seems to me that 95% of database usage is for non-transaction intensive information (I could be wrong in this). Given this I see increasing usage of triggers for doing various necessary things that regular reporting does not do. Am I just dreaming or that triggers are being increasingly used? Are performance concerns not important in the above context as long as you are ...

  1. #1

    Default How widespread is the use of triggers these days and how concerned about performance?....

    Fifteen years ago when databases were primarily used in the context of
    transaction-intensive applications, triggers were a nice feature of a
    DBMS system that did not get used because of performance concerns.

    Fast forward 15 years, it seems to me that 95% of database usage is
    for
    non-transaction intensive information (I could be wrong in this).
    Given this I see increasing usage of triggers for doing various
    necessary things that regular reporting does not do.

    Am I just dreaming or that triggers are being increasingly used?

    Are performance concerns not important in the above context as long as
    you
    are mindful of stuff like cascading triggers and keeping the trigger
    code short?

    Thanks in advance for your insights, experiences and opinions.

    regards
    Nari

    Nari Kannan
    Nari Guest

  2. #2

    Default Re: How widespread is the use of triggers these days and how concerned about performance?....

    Hi Nari.

    I'm not able to provide a Gartner style industry assessment, but my 20c
    worth is that my observations are of REDUCED overall usage of triggers due
    to improvements in basic features in the SQL / T-SQL languages & SQL Server
    itself (& other RDBMS products). One example of this is cascading delete /
    updates. In earlier versions these had to be implemented as triggered
    actions, but can now be defined directly in DRI statements (foreign key..
    references.. on delete / update).

    On the other hand though, inclusion of INSTEAD OF triggers against views in
    SQL Server 2000 is a feature that makes trigger programming attractive in
    many scenarios as it allows more design / programming options / flexibility.
    I've noticed growth in the use of triggers against views because of this.

    As for performance - obviously there's a performance hit whenever code path
    is added to a computer program & triggers are a particularly sensitive area
    given their low level (below physical table) nature, but as always there are
    efficient and inefficient approaches to programming & programming triggers
    is no exception. I've personally been surprised at how often cursors are
    rolled into triggers, reducing the performance of the DB to row by row
    record processing. This is obviously particularly harmful to triggers.
    Another alarming trend that seems to be emerging is the desire to hook
    triggers into external libraries or message queues. From an architectural
    point of view this has such obvious problems but it just seems that more &
    more people are trying to do this these days, even launching http services
    in triggers is discussed frequently in these newsgroups.

    Regards,
    Greg Linwood
    SQL Server MVP

    "Nari Kannan" <net> wrote in message
    news:google.com... 


    Greg Guest

  3. #3

    Default Re: How widespread is the use of triggers these days and how concerned about performance?....

    Nari,

    I currently oversee the development of a large .NET financial application.
    I also have primary responsibility as DBA and back-end development. When
    we're done, we'll have approximately 500 tables. About the only thing we're
    using triggers for is for auditing purposes. When certain records are
    changed, we store the original in an audit table. We use triggers for this.
    DRI with cascading deletes greatly reduces the number of triggers we need.
    All our business logic is in C# classes with a very small amount in stored
    procs. Triggers can be extremely useful but I always use them with caution.
    They can be a royal pain to deal with.

    J.R.
    Largo SQL Tools
    The Finest Collection of SQL Tools Available
    http://www.largosqltools.com

    "Nari Kannan" <net> wrote in message
    news:google.com... 


    Largo Guest

Similar Threads

  1. #39560 [NEW]: Inconsistent behaviour of strtotime when days > days in month
    By php at colin dot guthr dot ie in forum PHP Bugs
    Replies: 1
    Last Post: November 20th, 05:21 PM
  2. Replies: 5
    Last Post: August 5th, 01:18 AM
  3. Replies: 4
    Last Post: August 5th, 01:18 AM
  4. Widespread Panic Concert Photos.
    By Paul Brecht in forum Photography
    Replies: 1
    Last Post: July 31st, 02:52 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139