November 30, 2008 at 1:05 am
What are some appropriate uses of triggers?
Tables can be created with ON DELETE CASCADE to implement a cascading delete so it does not appear that after delete triggers are required in this scenario.
It seems like post-insert triggers can be replaced with SS replication techniques in many cases (although I'm not sure to what extent SS uses the trigger architecture to perform replication).
Do you tend to use triggers mainly as a workaround or quick fix to problems?
If you are designing a system from scratch can you generally avoid triggers? If not, then when is a trigger the ideal implementation choice over other implementation methods?
November 30, 2008 at 2:49 am
I typically use triggers for auditing, though that use is getting reduced or eliminated on SQL 2008.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 30, 2008 at 3:16 am
GilaMonster (11/30/2008)
I typically use triggers for auditing, though that use is getting reduced or eliminated on SQL 2008.
In what way?
November 30, 2008 at 3:48 am
Change tracking
Change Data Capture
SQL Audit
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 30, 2008 at 8:02 am
I agree with Gail... those are about the only things I use triggers for. That includes the ability to keep people from changing DateCreated/Modified or CreatedBy/ModifiedBy columns. Other than that, most of what people use triggers for can be replaced by some well formed constraints, just like you said.
The only thing that I can actually see a trigger being needed for, is if someone made the mistake of deciding that a foreign key table should be in another database... then, the trigger acts as a bit of DRI.
I typically avoid triggers because they basically make just about anycode run slower.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 30, 2008 at 9:30 am
GilaMonster (11/30/2008)
Change trackingChange Data Capture
SQL Audit
Just to be clear, those are three new features in SQL 2008 that can remove the need for triggers for auditing, depending what you want to audit, how and what extra data you want to capture with the audit.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 30, 2008 at 9:52 am
... and I was talking a bit more genericly...
--Jeff Moden
Change is inevitable... Change for the better is not.
November 30, 2008 at 12:37 pm
I also, pretty much only use DML triggers for Auditing and constraint implementation where the built-in constraints facilities will not work.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 30, 2008 at 12:40 pm
Hmm, thinking about it, I realize that I also have used triggers for custom replication-type solutions and as an input source for Service Broker applications.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 1, 2008 at 12:11 pm
A lot depends on the application that is using the database. I work extensively with a Point Of Sale application that needs customization for different clients that have different needs. The application allows for adding columns to existing tables, but it doesn't really know what to do with these new columns. The new columns would typically be used in some sort of web application (an add-on type application) that uses the database tables.
I mostly use triggers to maintain these additional columns where the application ignores them so they can be displayed or maintained from the web application. I only use them when absolutely necessary.
As others have stated, triggers can be very overdone. I've seen whole applications where the business rules were implemented in triggers. These applications were nightmares to deal with.
Todd Fifield
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply