x

Trigger question for DBAs

In the spirit of Matt Whitfield and fostering discussion...I've noticed several trigger questions lately, as a DBA, do you allow triggers on your production dbs? I'm interested in the rationale for and against.

more ▼

asked Nov 25, 2009 at 12:08 AM in Default

Scot Hauder 3 gravatar image

Scot Hauder 3
121 2 2 3

Hi Tom, good eye! the magic of not being a registered user and using 3 different computers. I'm registered on SSC but when I click the SSC icon to use an openid through the site it does not work..hangs
Nov 25, 2009 at 12:53 AM Scot Hauder 3
Well, I had seen your name before with a rep in the 50's, but now it says it's 21. I figured either you had multiple accounts or you got several answers very wrong. ;)
Nov 25, 2009 at 01:10 AM Tom Staab
(comments are locked)
10|1200 characters needed characters left

8 answers: sort voted first

I'm not a DBA, but I've been asked to play one from time to time. I like the completeness of triggers (as long as security best practices are followed instead of everyone using "sa"). I like the comfort of knowing, for example, that the code will execute after an insert regardless of whether the insert was caused by the typical process in the UI or someone running T-SQL directly against the database or any other way. This is especially important if the trigger is used for auditing.

more ▼

answered Nov 25, 2009 at 01:08 AM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

Thanks Tom, so you see them most often used for auditing purposes? I am curious if there is an adverse affect on high volume activity dbs. I guess from a developer perspective I tend not to like "things" happening when I didn't explicitly code for them to happen. Besides the auditing application, I think they would add unnecessary complexity for tracking down problems and added administration hassle. Just wondering what all you think...
Nov 25, 2009 at 01:21 AM Scot Hauder 3

Scot, I am with Tom. I make extensive use of them, but almost entirely for auditing.

But yes, they are additional code so they will always bring in more overhead and take more time when called. How much that matters can vary though.
Nov 25, 2009 at 11:33 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

I use INSTEAD OF triggers quite a lot when I want to change the functionality of a system. For example, on my company's website, we have a bug tracker system, which expected to find it's own tables etc. Instead of writing functionality to synchronise user accounts between the main site and the bug tracker site, we use views which match the schema of the bug tracker tables, and then use INSTEAD OF triggers on those views to perform the relevant operations in our native tables. I find that sort of thing invaluable.

Also, as Jack says, the 'protecting me from me' bit can be important if you're in a pressured environment and you need quick, guaranteed results. As Håkan (sorry I have no idea how to get the special a mate! - aha - thanks Kev) says, you need to be mindful of performance, so it pays to test, test and test again!

more ▼

answered Nov 25, 2009 at 05:52 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

å - Alt+0229 :)
Nov 25, 2009 at 06:36 AM Kev Riley ♦♦
Nice one - cheers mate :)
Nov 25, 2009 at 06:42 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

As a rule, I'm definitely in the 'No' category.

Hate it when business logic, or workflow is implemented as triggers. If you need to do something to some data, as part of a process, then put it with the rest of your code in the SP. Code should be somehwat self-documenting, but splitting the workflow out between an SP and a trigger on the underlying tables just breaks that notion.

Having said that, I do use triggers, but as most have said here already, mainly for auditing purposes.

more ▼

answered Nov 25, 2009 at 05:56 AM

Kev Riley gravatar image

Kev Riley ♦♦
52.8k 47 49 76

(comments are locked)
10|1200 characters needed characters left

Do I allow them? Yes. But do I encourage them? Heck no. I realize they have their place in a well designed database system, but I've noticed that more often than not they are either used incorrectly or as a crutch to try to prop up some bad code or bad structure. So, I look at them with a VERY jaundiced eye when they're coming my way.

By the way, the re-launch of the SQL Server Standard had an article on triggers in it. It's worth checking out.

more ▼

answered Nov 25, 2009 at 10:52 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.7k 19 21 74

(comments are locked)
10|1200 characters needed characters left

This is a classic "It Depends" question.

I typically only use them for auditing, but I also have used them to enforce some RI (FK could be from one of 2 tables). I've also used them in an EAV design to populate the attributes with either null or default values when an entity was created and when a new attribute was created.

Like Tom, I like that the trigger fires when something happens outside the normal application bounds. I call this "protecting me from me".

They definitely add some overhead and poorly written ones are total performance killers.

more ▼

answered Nov 25, 2009 at 01:34 AM

Jack Corbett gravatar image

Jack Corbett
1.1k 2 2 3

I totally get the 'protecting me from me' bit - a lot of times when you've got a very complex solution, it can make sense to do something with triggers.
Nov 25, 2009 at 05:48 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x117
x61
x21

asked: Nov 25, 2009 at 12:08 AM

Seen: 1372 times

Last Updated: Nov 25, 2009 at 11:34 AM