The Helpful Triggers

Question

The Helpful Triggers

Steve Jones - SSC Editor

SSC Guru

Points: 736494
More actions
September 10, 2018 at 9:14 pm

#363042

Comments posted to this topic are about the item The Helpful Triggers

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

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

David.Poole SSC Guru Points: 76035 More actions · Answer 1

I think triggers work particularly well when used as a pattern. An example would be to maintain Type 4 slow changing dimensions.
I've also used them on tables representing a hierarchy to prevent people deleting the root node of that hierarchy. On rare occasions I have used instead-of triggers on views.

Apart from their visibility (the lack of) I think the biggest pitfalls are:-

Coding to handle single record events rather than set based events

Not thinking trigger = light weight signal. Stuffing too much into the trigger.

Using them as a work-around for functionality that should be in the application

LinkedIn Profile

Jeff Mlakar SSCrazy Points: 2879 More actions · Answer 2

I want to say I enjoy the T-SQL Tuesday events very much and am glad we have them. Triggers always give me pause and raise suspicion. Maybe I don't give them a fair shake but I've seen this misused more than used and it can make troubleshooting much more difficult.

Rod at work SSC-Dedicated Points: 34017 More actions · Answer 3

I've used triggers in the past, to help track when someone inserted, modified or deleted some client data. Since the data involved was under HIPAA we needed to have a way of tracking when someone created, modified or deleted data. And since more than 1 application could do these things, I thought it best to do this with a trigger, rather than hope the developers would remember to do this. But this was the only thing I did with triggers, so overall it was a very small part of everything that was done.

In my current job I don't have enough control to introduce triggers.

Kindest Regards, Rod Connect with me on LinkedIn.

thisisfutile Hall of Fame Points: 3668 More actions · Answer 4

I read a Joe Celko post once that made me laugh, because I agree and try to follow this advice. "I believe that a person should write no more than 5 triggers per …. career".

roger.plowman SSChampion Points: 10265 More actions · Answer 5

I tend to use triggers as audit log writers--and NOTHING else...

t.ovod-everett Default port Points: 1487 More actions · Answer 6

The DDL is separate from the table

This tells me you're not modifying the SSMS defaults for script generation. There are a plethora of options I update in the SSMS defaults, and one of them is the Scripting settings. Some of the defaults can be dangerous (SET ANSI PADDING can cause issues if one concatenates scripts without paying attention). The ones I personally adjust are:

Include descriptive headers: False
Script USE <database>: False
Generate SET ANSI PADDING commands: False
Script full-text indexes: True
Script indexes: True
Script triggers: True

Stefan LG SSCommitted Points: 1936 More actions · Answer 7

The scenario that Steve described is EXACTLY the same scenario for which we have used triggers before.
So maybe that is a good (the only?) use case then?

Apart from that, they have just given me gray hair...

Rick-153145 SSCrazy Points: 2823 More actions · Answer 8

Using triggers for audit is fine, but they should always be kept simple and never nested, as soon as you start nesting or adding more complex code, you are asking for trouble.