Post reply

More Triggers

  • November 11, 2010 at 9:00 pm

    #147561

    Comments posted to this topic are about the item More Triggers

  • November 11, 2010 at 10:14 pm

    #1249831

    Hello!

    Happy Friday :-)!

    I am of the opinion to have 1 trigger for business logic (per operation - Insert/Update/Delete) and 1 trigger for auditing. The prime reason being - it is easier to manage that way. Also, in the systems that I work with the amount of business logic happening in triggers is minimal, which makes this approach feasible to use.

    Have a good week-end, everyone!

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com

    Follow me on
    Twitter: @sqltwins

  • November 11, 2010 at 11:43 pm

    #1249844

    Hi

    I agree with Nakul. Also we can use triggers for validating complex data constraints (based on buisness logic) which cannot be done using normal constraints.

    Regards

    Yoganand

    🙂

  • November 12, 2010 at 12:08 am

    #1249847

    I also belive in multiple triggers. I have one trigger for auditing, plus additional triggers (often more than 1) for other purposes. I find it is easier to manage the triggers this way.

    Mal

  • November 12, 2010 at 12:47 am

    #1249857

    I prefer multiple triggers. But, it's easier to fall in recursive triggers.

  • November 12, 2010 at 1:58 am

    #1249877

    I've always preferred to use views rather than tables for most of the grunt work so I end up with business logic in the view triggers and auditing in the table triggers.

    We don't audit every table so that keeps it simple and we only have a few updatable views. Nobody has permissions on tables apart from developers and all the permissions are on the views.

  • November 12, 2010 at 2:43 am

    #1249883

    I think a separate trigger for auditing is definitely preferable.

  • November 12, 2010 at 5:41 am

    #1249938

    paul s-306273 (11/12/2010)

    I think a separate trigger for auditing is definiteley preferable.

    Me too Paul. I also like to know who (or prevent) might be modifying tables, so I like DDL triggers as well. 😀

    "Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"

  • November 12, 2010 at 6:14 am

    #1249958

    As with most of the replies, I use one trigger for Auditing and one or more triggers for "other". I prefer to limit my trigger use, so I have minimal "other" triggers, but always have an "audit" trigger on DML tables.

    It's easier to manage when triggers are functionally separate.

  • November 12, 2010 at 6:15 am

    #1249960

    If possible, I prefer to avoid triggers altogether.

    Having said that, on tables where business rules can be enforced using DRI and constraints, I use them for auditing.

    However, my developers have been slow to move some of our apps to 100% stored procs, so some triggers are still in use for business rules in the meantime. As the apps move to stored procs, the triggers logic are phased out.

    1 trigger / action.

    Happy Friday.

  • November 12, 2010 at 7:47 am

    #1250018

    Happy Friday All!!!

    I prefer the separation of triggers for auditing and business rules (1 or more). However, as I mostly support another vendors databases, I am seeing more developers move the business logic into the application code and out of stored procedures and triggers in the database.

  • November 12, 2010 at 8:40 am

    #1250068

    I prefer not to use triggers. IMO business logic is better stored elsewhere and in a less global fashion. Triggers are very heavy handed. Auditing triggers on a dev environment are acceptable, but again I would prefer a different auditing solution.

    I've worked on a system that was all triggers. There were no procedures or other logic. Each different action an app this database fed would perform would fire off 5 or 6 different triggers in tables. It was a nightmare to map processes, and figure out how to adjust the system without screwing up all the other triggers. I would much rather have all that logic in a procedure, not only because it is all contained in one location but also because it's execution can be controlled much easier and protected from mixing with other processes.

    Kind of the same policy I have about cursors. Don't do it!

    -------------------------------------------------------------------------------------------------
    My SQL Server Blog

  • November 12, 2010 at 8:41 am

    #1250069

    Jeff Kunkel-812485 (11/12/2010)

    Happy Friday All!!!

    However, as I mostly support another vendors databases, I am seeing more developers move the business logic into the application code and out of stored procedures and triggers in the database.

    Unfortunately, we are installing a new ERP that all check, logic, auditing etc. are in the app. Triggers, stored procs, constraints, primary key, clustered index are just a dream.

  • November 12, 2010 at 9:38 am

    #1250118

    Triggers, in my experience, are best used sparingly and with careful forethought. Triggers that reference other database objects add complexity to query plans.

    I worked on a system (in DB2) that had huge numbers of triggers on improperly designed tables, and which had multiple schemas for stupid business reasons. You'd have a main order table with about 20 triggers on it - changes to this table cascaded through those triggers to probably 10, 12 tables, each of which had multiple triggers that cascaded change to tables that had triggers that cascaded change... to make matters worse, if you have multiple schemas with identical tables, the optimizer can't determine which one will be affected and so the plan has to take all of them into account, each with THEIR triggers.

    We actually had developers write stored procedures that couldn't be compiled because there wasn't enough memory on the server to prepare the plan.

    Triggers are indispensable in some (relatively rare) cases, and should be avoided at all other times.

  • November 12, 2010 at 9:39 am

    #1250121

    I used to manage a huge casino management system and was forced to use triggers to audit tables that were updated by stored procedures. Altering vendor code is never a good idea, so placing a trigger on the table was a great work around. My first choice however, would be to modify the stored procedures that input the data and add a audit task instead. Triggers are tricky and can lockup tables if not written carefully for the system applied to it. Table locks are your worst nightmare in a high transaction live entertainment environment. 😉

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply