appropriate use of triggers

Question

appropriate use of triggers

sqlguy-736318

Hall of Fame

Points: 3859
More actions
November 30, 2008 at 1:05 am

#203631

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?

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

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlguy-736318 Hall of Fame Points: 3859 More actions · Answer 2

GilaMonster (11/30/2008)
I typically use triggers for auditing, though that use is getting reduced or eliminated on SQL 2008.

In what way?

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

GilaMonster (11/30/2008)
Change tracking
Change 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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

... and I was talking a bit more genericly...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

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]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

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]

tfifield SSCrazy Eights Points: 9655 More actions · Answer 9

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