April 18, 2016 at 7:47 am
manie (4/18/2016)
roger.plowman (4/18/2016)
Personally, I use three triggers on *almost every single table*--BUT 😀I only use them to create an audit log of insertions, deletions, updates and updates where I have to save the old value.
[...]
Limiting trigger use to this purpose moots trigger opacity.
Thanks Roger, This is exactly what I use them for and it is really handy with audit logging.
May I recommend you to take a look at SQL Audit?
https://technet.microsoft.com/en-us/library/dd392015(v=sql.100).aspx
License a part, this is the best way to go on a reliable audit solution..
April 18, 2016 at 7:55 am
manie (4/18/2016)
roger.plowman (4/18/2016)
Personally, I use three triggers on *almost every single table*--BUT 😀I only use them to create an audit log of insertions, deletions, updates and updates where I have to save the old value.
That way I only have to pay attention to them when:
1) I create them (using a template to make it consistent)
2) An audit logging need changes (tighten/loosen requirements)
3) I change the table structure (either deleting or renaming fields the triggers use)
Doing it this way I find the only trigger I really have to worry much about is the update trigger -- and then only for reason #3. The Insert/Delete triggers pretty much take care of themselves.
Limiting trigger use to this purpose moots trigger opacity.
KISS -- Not Just A Pretty Acronym :hehe:
Thanks Roger, This is exactly what I use them for and it is really handy with audit logging.
I can chime in on this. We had no triggers in our shop - but I did some research on them and implemented them this very winter for exactly this reason, too.
I can't get the money/resources necessary to build proper front-end tools - but I have a desperate need to delegate data maintenance, instead of receiving a few hundred records (some of which is altered) every now and then with a "can you please update the system with these quotes/rates/names/etc.". Since I am also not the merry owner of an SQL Server Enterprise version, there are a lot of things, I can't do smart. So the solution is (for the time being) to move the audit logging to the table level, to prevent users with access rights from making "quick'n'dirty" changes to tables, circumventing front-end tools (or manual scripted procedures).
I find the triggers very neat.
But beware: No multi-record changes! (and that limits the scope of colleagues I can delegate to rather much...!):hehe:
And I would love to see more transparency in their existence and execution, just as mentioned by others. Different icons for the tables, and them showing up in tabs: Great ideas!
I would never use triggers to implement business logic where a long list of actions are executed as pearls on a string. I would find another way for that (but maybe having a few triggers in central places, I wouldn't deny).
April 18, 2016 at 7:58 am
I was the developer of a system that registers medical people that want to volunteer in disasters/emergencies. The DBA that was assigned to the project did a brilliant job in creating the database. He used triggers to create an audit log.
He also created a trigger to send an email to the coordinators when a new volunteer registered. However, that caused a problem when the database was moved to a different server since he had left. The account had to have a mail profile created.
April 18, 2016 at 8:05 am
But beware: No multi-record changes! (and that limits the scope of colleagues I can delegate to rather much...!)Hehe
Why not? Properly written triggers can handle multi-row changes with no problem.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 18, 2016 at 8:09 am
I completely agree. I've used triggers to notify me when someone tries to do something they shouldn't, otherwise I avoid them.
True story -- I just had an interview where the person asked me 4 ways to implement referential integrity in a DB. I could only think of a couple. One of their ways was "triggers!"
I thought that maybe I wasn't a good fit for that job. 😛
Lewis
April 18, 2016 at 8:49 am
Gary Varga (4/18/2016)
Whilst I don't "distrust and dislike them", I agree that the issue with triggers is not a database engine problem but one of tooling. Their existence needs to be more obvious. From that transparency will follow.
Added a note to include a change in SQL Prompt to help here: https://redgate.uservoice.com/forums/94413-sql-prompt/suggestions/13468245-change-table-icon-for-trigger
Feel free to vote.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 18, 2016 at 8:51 am
manie (4/18/2016)
I have noticed on SSC that many times people say this is bad or that is bad and have not yet seen concrete evidence of why certain things are bad. For instance, and I don't want to cause an uproar now, cursors. I am not going to go into that now because we are not talking about cursors now.I found one day that somehow someone gained access to the database and deleted some data. Now I must say we were 3 persons that had admin access to the database and everybody said they did not do it. So, I had to find a way to trap the person that did it and thus created a trigger on that table that will show me what was deleted/updated/inserted and who did it. It was indeed one of the persons that had admin rights and of course that persons rights was revoked. So, as with anything in SQL if you abuse it, it will abuse you back. :cool::cool::cool::cool::cool:
Triggers aren't good or bad. They're often poorly coded, but as you noted, that's an abuse of SQL.
They are a bit hidden, they also have implications of taking actions on every DML statement, which isn't often what people want. If you know how to use them, they're a nice tool. However they are one that can add overhead to a system, which needs to be considered in architecture and planning.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 18, 2016 at 8:52 am
call.copse (4/18/2016)
Personally I'll use them if there is no other elegant way of solving a problem evident. Essentially when they appear to be the least of all evils. This happens reasonably frequently in my experience, but not on every project or particularly extensively.Doesn't mean I don't curse when I find them causing some bizarre unanticipated behaviour.
+1
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 18, 2016 at 8:53 am
xsevensinzx (4/18/2016)
I have never used triggers. I guess I do things the hard way, but I read too many books that said triggers on data warehouses were bad when dealing with large quantities of records. 😛
Not good or bad. They add overhead. The level of overhead might be trivial or problematic, but it's there, and as with most things, it depends if this is acceptable for your system.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 18, 2016 at 9:19 am
I have only used triggers once in my entire career. And it was a case where we needed to keep a "current balance" for serveral thousands accounts in order to authorize/deny the next transaction. Previous to this, EVERY transaction had to SUM all previous ones to compare against a limit and then decide to authorize or not. Besides this extreme case, I have never needed one.
April 18, 2016 at 9:53 am
I've learned to avoid them over the years particularly the more complex the database is. I've seen them used by developers to 'fix' what are essentially design/security/management flaws. For example, access to a table may or not be under their control and they can't be sure where a write will come from but they want it logged so they slap a trigger on the table. Problem solved!
I think with triggers you should be particularly careful with what you ask for, but I'm not prepared to completely condemn them...yet.
April 18, 2016 at 10:09 am
My first exposure to SQL Server was with an application that made extremely heavy use of database trigger, they stored an awful lot of application logic in them. Because we made such heavy use of triggers we didn't suffer from the "hidden" aspect, and the majority of the architectural issues with the application didn't have anything to do with they fact we were using triggers.
I think that the application was a child of it's time, written as a 2-tier application on SQL Server 6.5 with a PowerBuilder front-end. And it was relatively flexible to extend for clients .... within certain limits. But I wouldn't build an application the same way now. Automated testing was a nightmare we never embraced. It was impossible to extend without changing the base stored procedures / triggers, automatically giving us an upgrade / support headache. For all the wonderful simplicity of this architectural model, it caused problems we didn't fully appreciate at the time, often mistaking activity created by the problems purely as "the cost of doing business".
But I've swapped that simple model, where my data is instantly accessible and modifiable, for a new programming model. I've embraced SOLID. I've embraced IoC. I've learnt to create a composable application that allows me to extend, replace or add functionality without completely trashing the upgrade path.
With modern programming languages, technologies and techniques I struggle to find a place for my beloved triggers. It's not that I'm worried that other developers won't know they're there, it's that I'm running out of places when they are the appropriate solution. But I'm not giving them up on them just yet. Or stored procedures!!!
April 18, 2016 at 10:19 am
In addition to Crouching Cursors and Hidden Triggers, some other pitfalls that can lead to the demise of a DBA are stored procedures compiled with non-default SET-ings, remote joins, and disabled check constraints / indexes.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 18, 2016 at 12:50 pm
I can understand deficits in terms of SSMS giving visibility but SYS.objects and INFORMATION_SCHEMA. ROUTINES identify them pretty clearly and also reveal lineage.
I'm reluctant to condemn any weapon in my arsenal just because it hasn't got a "pointy bit this end" sign on it.
Similarly any tool can be abused.
I've worked with triggers in various scenarios
1. Auditing
2. Queue table maintenance
3. ORM mitigation
4. Signal generation
5. Instead Of triggers on views during refactoring exercises
The traps are
1: failing to design for multi-record use
2. Using them for heavy weight processes rather than small lightweight activities
3. Failing to consider dependencies this causing dead locks.
April 18, 2016 at 3:19 pm
I couldn't agree more with the premise. Great volumes of time can get lost because the behaviors of triggers is not always obvious to developers.
Don Simpson
Viewing 15 posts - 16 through 30 (of 51 total)
You must be logged in to reply to this topic. Login to reply