0

Im planning to implement triggers on all my DML statements for log management. So please identify if there are some issue or drawbacks to do so.

Fraz Sundal
  • 10,288
  • 22
  • 81
  • 132
  • What kind of drawbacks are you looking for? Performance? Maintainability? Other? – Oded Jul 03 '10 at 08:32
  • Basically im worried about the load, beacuase the application have a centralized database and almost 100 CMS based websites will be running on it. – Fraz Sundal Jul 03 '10 at 08:46

2 Answers2

5

I think this SQL Server MVP blog post sums it up best:

  • Triggers are difficult to find in SSMS
  • Trigger execution cannot be seen by the client application
  • Trigger logic can be difficult to track
  • The inheritors of your code might not realize triggers have been implemented
  • Triggers are known for inhibiting performance

Brent Ozar also calls out triggers as his #10 offender in The Top Ten Developer Mistakes That Won't Scale

But in the spirit of being balanced, this blog post lists some benefits:

  • Ability to enforce more complicated business logic
  • Can use as alternative means to fire a scheduled task
  • Useful to audit any change in a table, regardless of how it happened (i.e. stored procedure, client application, ad hoc query)
Community
  • 1
  • 1
8kb
  • 10,956
  • 7
  • 38
  • 50
3

You must use TRY/CATCH so their behavior is more logical

That is, they will abort your batch otherwise and subsequent statements are not run (after an INSERT say that is rolled back in the trigger). Although you'd expect this on failure it can be a pain. However, TRY/CATCh

Otherwise, they can catch folk out, for example, when you're trying to do a bulk load and forget they are there. But they are perfectly valid SQL features that can be used appropriately.

Another gotcha is where some idiot wants to send emails per row in a trigger or otherwise do a loop or some such muppetry.

And the ubiquitous link to Erland Sommarskog's article on error handling

gbn
  • 422,506
  • 82
  • 585
  • 676