Triggers: Good, Bad, Mostly Ugly

Bob Pusateri walks us through a poorly-written DDL trigger:

First, the scope. While the application that deployed this trigger has its own database, AppDB, this trigger is firing for events on the entire server, which is what the ON ALL SERVER line means. Any qualifying event on this server, even if it pertains to another application with a separate database, will be written into this database. And what is a “qualifying event”? Literally any DDL statement. The line AFTER DDL_EVENTS specifies the very top of the event hierarchy used by DDL triggers.

So to recap on scope, this application is capturing all DDL statements on the entire server and saving a copy for itself. This application is seeing (and recording) plenty of events that it has no need to see. If this were a healthcare application or a system that dealt with PII it would be a legal nightmare, but fortunately it isn’t.

However, scope isn’t the only issue.

Worth the read.  If you use DDL triggers on the instance level, make sure you know what you’re looking for and limit yourself as much as possible.

Related Posts

Death Marches Roundup

Jeff Mlakar takes us on a tour of bad project planning: This month we had 15 post submissions about this daunting topic. Two of the posts were from people who had never posted before. To them I say welcome and I hope you enjoyed the experience. I think, in general, you were all brave to […]

Read More

Analysis Of A Failed Project

Eugene Meidinger looks back at a big project which fell apart: So the first issue was that the software was built in-house by another company in the same industry. Imagine, for example, if a large bakery had created an ERP system and another large bakery wanted to move to that system. Sounds great, right? Well, […]

Read More

Categories

September 2018
MTWTFSS
« Aug Oct »
 12
3456789
10111213141516
17181920212223
24252627282930