April 25, 2005 at 8:51 am
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 ...
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
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:
DML triggers can cascade changes through related tables in the database; however, these changes can be executed more efficiently through cascading referential integrity constraints.
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
April 25, 2005 at 12:48 pm
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
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
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
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
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
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
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