Post reply

Why use triggers?

  • April 25, 2005 at 8:51 am

    #88731

    Hello all.

    I'm the lone DBA (who also does a lot of development) in my shop, surrounded by a good number of accomplished developers. Recently, one of the developers started an internal discussion thread on the need for triggers.

    It seems that most of the developers here do not like triggers, citing the fact that they represent 'invisible code' that can be difficult to track down. One developer went so far to say that he has never needed a trigger in all his development career.

    There have been a few positive points made about logging changes but that seems to be the only argument put forward thus far in favour of them.

    I have yet to weigh in on my thoughts and I thought I would put this question out there. In my many years of programming, I have found numerous situations where triggers were beneficial including ...

    • situations where our shop has adopted a third-party application that was not quite doing what we needed it to
    • tracking down strange application issues
    • enhancing simple applications such that inserts/modifications had a transactional effect on multiple tables

    Any other comments on the use of triggers and why they couldn't just be handled with code outside the database tier?

    - Mike

  • April 25, 2005 at 9:00 am

    #554615

    Here's some info from BOL :

    Constraints and DML triggers each have benefits that make them useful in special situations. The primary benefit of DML triggers is that they can contain complex processing logic that uses Transact-SQL code. Therefore, DML triggers can support all of the functionality of constraints; however, DML triggers are not always the best method for a given feature.

    Entity integrity should always be enforced at the lowest level by indexes that are part of PRIMARY KEY and UNIQUE constraints or are created independently of constraints. Domain integrity should be enforced through CHECK constraints, and referential integrity (RI) should be enforced through FOREIGN KEY constraints, assuming their features meet the functional needs of the application.

    DML triggers are most useful when the features supported by constraints cannot meet the functional needs of the application. For example:

    • FOREIGN KEY constraints can validate a column value only with an exact match to a value in another column, unless the REFERENCES clause defines a cascading referential action.
    • Constraints can communicate about errors only through standardized system error messages. If your application requires (or can benefit from) customized messages and more complex error handling, you must use a trigger.

    DML triggers can cascade changes through related tables in the database; however, these changes can be executed more efficiently through cascading referential integrity constraints.

    • DML triggers can disallow or roll back changes that violate referential integrity, thereby canceling the attempted data modification. Such a trigger might go into effect when you change a foreign key and the new value does not match its primary key. However, FOREIGN KEY constraints are usually used for this purpose.
    • If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger execution but prior to the AFTER trigger execution. If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not executed.

    ******************
    Dinakar Nethi
    Life is short. Enjoy it.
    ******************

  • April 25, 2005 at 12:48 pm

    #554677

    Being someone who is also both in the DBA and developer role, my experience has been that Triggers are very useful tools if used judiciously.

    In one case using a trigger to import data into a report table (25 million rows) helped reduce the output time for the report from 7 hours to about 45 minutes. The trigger enabled the import to occur simultaneously when original data was imported into the primary table from text files. In this case though I made a note in the stored procedure that the step of importing the data was now being handled by the trigger.  If someone else was trying to track it down in the future they could then follow the path.

    In other cases I have used triggers to populate "Deleted" tables with data that was being deleted from the primary tables. Updating date fields such as a DateModified field is another good use for Triggers.

    Yes you could manage all of this through Stored Procedures, but not without writing a lot of extra code, which in itself can increase the complexity and confusion for someone trying to troubleshoot a problem.

    I would say don't be afraid to use triggers, but leave a trail in the way of documentation so you and the developers are not pulling your hair out if a problem does occur.

     

    John

  • April 25, 2005 at 11:39 pm

    #554749

    They are also very useful if you are trying to capture changes to some particular data fields to apss through to other databases to allow data links between databases for different 3rd party apps - e.g HR system to Accounts system to capture user name changes etc - the advantage of a trigger is 1. you probably haven't got the code of the HR system etc and 2. it will catch change however it is made (e.g. direct SQL, App front end etc)

    James Horsley
    Workflow Consulting Limited

  • April 26, 2005 at 3:05 am

    #554794

    Just to add a developer's (and supporter's) tuppence:

    A few triggers are ok if they are really what you need - the examples given so far are good. I would underline the message that any code running with referential integrity in mind ought to be done explicitly to keep the developer sane.

    However, trying to understand a system with lots of them is a complete nightmare and really puts the effective complexity up fast. Triggers that fire other triggers are particularly nasty when trying to debug things one step at a time.

    When I have my code review hat on, any trigger has to be very well justified indeed, because one of my main concerns is maintainability. In 9 years development with Oracle and SQL Server, I have written just one, but must have spent several working weeks trying to cope with the consequences of those written by others.

     

    Bill.

  • April 26, 2005 at 6:58 am

    #554850

    We have a rule that we don't embed business logic in triggers, nor do we use them to do something (such as enforce RI) that can be done with a more "native" approach.  We do have a trigger on every table that we have--used to update our audit fields (who, when).  This ensures that no matter what happens, we have the real update information for audit purposes.  No one can miss the update in code, change the information with an ad hoc query, or forget entering the information.  This is helpful in maintaing the integrity of our night delta process for populating a warehouse.

  • April 26, 2005 at 7:01 am

    #554853

    Every table I have has a trigger on Update to track changes made to the record at the database level.  All tables have fields of "UpdatedBy" and "UpdateTime" which are filled by a trigger.  The reason we don't do it in the the application is because we want to track updates that might occur outside of the app.  Our triggers also write audit records for the same reason.  It is our shop standard to use triggers for these activities.  Referential integrity is handled by indexes and constraints, not triggers or application code.  A few "constraits" that involve combinations of values in multiple fields are done in triggers but most are handled by table constraints.  Since triggers are a standard in our shop we don't think of it as "hidden" code.  By the way, I'm an application developer.  At our site, the developers define the triggers that are needed, they are only applied to the database by the DBA's.

    Terri



    Terri

    To speak algebraically, Mr. M. is execrable, but Mr. C. is
    (x+1)-ecrable.
    Edgar Allan Poe
    [Discussing fellow writers Cornelius Mathews and William Ellery Channing.]

  • April 26, 2005 at 9:48 am

    #554947

    Terri, you've hit on one of the primary benefits of using triggers.  The closer any data manipulation is to the data, the less risk that it will be manipulated incorrectly.  It's not the only solution, but keeping data constraints and manipulation code as close as possible to the data is a good practice.

    I can't tell you how many times I've been told that "the data will NEVER be managed except by this one application" only to have a request come up within weeks for another process/application/spreadsheet to update the data.

    I find the most difficult part of managing triggers is how one views and updates them in EM.  If your developers use a third party tool, triggers are easier to view, update, and manage.

    Karen

  • April 27, 2005 at 5:53 am

    #555167

    Thanks everybody for the great comments. - Mike

Viewing 9 posts - 1 through 8 (of 8 total)

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