April 17, 2016 at 1:47 pm
Comments posted to this topic are about the item The Hidden Trigger
April 18, 2016 at 12:26 am
On the whole I agree as unless you have either worked on a system that has a lot of triggers or have your fingers burnt then they tend to be the last place to look at for issues. Also excessive use of triggers for business logic can be a nightmare and all too often turn future work into the lava-flow anti pattern ( http://antipatterns.com/lavaflow.htm ).
But on the positive side, if all the business logic is in triggers then the number of data repairs does diminish
and the data is consistent. Does it really matter if the database server needs a bit more grunt to run the triggers if the performance is good enough.
April 18, 2016 at 12:34 am
Interesting editorial.
Most people have a negative view on triggers, often for the reason's Steve's given. I agree they can be problematic when poorly implemented (all too common I'm afraid), but sometimes they are the ideal fit for a problem.
I've only implemented about ten triggers over fifteen years. In each case a trigger was the perfect solution. Use them when you need to ensure data consistency in the table regardless of the input mechanism, but always consider whether there's an alternative. Fully agree it would be great to have a trigger execution plan in SSMS - I too have wasted time looking for issues caused by triggers before.
April 18, 2016 at 1:10 am
I have "grown up" with triggers as part of the ERP system I implement and support. EVERY table has 3 triggers for Add, Delete and Update. I think the developers could have been more creative by using stored procedures better, but then again, keeping each table's rule sets in one place is rather convenient. The one thing worse than the triggers is that they have encrypted everything, so I have to create a decrypted db to do fault finding. I also would hugely benefit from a trigger tab in SSMS.
April 18, 2016 at 2:26 am
Triggers are my nemesis.
The ERPs I manage come out in many flavours, it has a lot of modules that can exists or not so the db structure depends on the actual installation.. at the end, the DB is something like 1200 tables, 400 views, 400 storedproc, 60 functions.. and 0 to 10 triggers.
So yes, I use triggers. Why? It's a cheap patch you know you are going to pay in the future, but you can't afford a better solution NOW.
ERP softwares are multi tier, usually there is a high level DOM (written in something that ranges from cobol to python, in my case java or c++) that implements the Business Logic, because an ERP is not a CRUD, it manages a company and apply rules!
And, when the software is installed, configured and running, you realize (because the final user let you know) that something is not as expected.. a bug is there. The user will always say it's a bug, he'll not tell you that's a missing feature, an aspect not analyzed, something coming in a next release.. it's broken, it's a bug, so you have to fix it.
Now you have two way to do that, correct the high level DOM tier, pass through the testing stage, plan a downtime for system update and have the system fixed (in a week..) or place an ugly trigger that fix it and fix it now.
Often, the latter applies.
If I'm lucky, I add a issue to my Jira installation, manage the bug correctly (ie. implement the missing feature) and in the next release I will have the system working smooth and not triggers at all in the db.. if i'm not lucky, the system stays with that trigger in it.
Often, the latter applies.
Having thousands of tables, when something strange apply i usually think that God may be the cause, the user is cheating, or a "bad" trigger is there.. in that order.
Even if you know there are triggers, even if you know there is a trigger on that very table, you'll still not sure why this trigger's faulting the system now, and why it didn't do it when it was just created. So you start spending time to analyze what the trigger does just to realize that it wasn't the cause.. then you start to analyze the second one of a long long list of triggers..
Some time it happens to know that a trigger have been disabled for a while, causing data not to be updated for a while a thou faulting the system.. Nothing in the system will let you know about that. Only an intuition can save you.
Some time it happens to know that a table where the system should insert a record at a time, had a multirecord insert instead, done by a new procedure ( or a very old procedure, or a seldom used procedure, or a not documented one), breaking that trigger, because it was not designed to handle more than a record at once. Nothing in the system will let you know about that. Only an intuition can save you.
Many other thing may happen that require intuition.. not to say that usually you have the trigger only in the production environment, and not in the development installation, causing the bug not to reproduce in you debugging installation.. (unless you were so wise to install the trigger even in a that db making the use of trigger not so cheap and extemporaneous )
At the end.. I realize that triggers are a bad but necessary tool. They help solve a problem fast, but are going to make bigger problem if you did not remove them fast. In case of trouble, they require a lot of intuition, the article focus on the fact that you need intuition to remember they exists.. but they require even more intuition to understand if they are (or worse if they were) the cause of a fault.
I'm sharing this in the hope to see a better future, a future where no triggers will harm our children.
Antonio Zerbinati.
April 18, 2016 at 3:31 am
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.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
April 18, 2016 at 3:47 am
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:
Manie Verster
Developer
Johannesburg
South Africa
I am happy because I choose to be happy.
I just love my job!!!
April 18, 2016 at 4:00 am
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.
April 18, 2016 at 4:53 am
I used to think it was just me who forgot about triggers I'd written just a short while beforehand. It is just like stepping on a rake in the garden that you've just put down. It hurts, but you just feel so foolish.
Best wishes,
Phil Factor
April 18, 2016 at 5:21 am
I agree with the visibility issue. I have written very few triggers as I tend to turn to them as a last resort. I have had a few bite me when strange things happened in the database and it always takes a while for me to add them to the troubleshooting equation. If there was a better indicator in SSMS of their execution (and frankly of their existence) then I might be more prone to using them. They certainly have their place and can be quite useful. I just don't like how they tend to get lost.
April 18, 2016 at 5:48 am
Phil Factor (4/18/2016)
I used to think it was just me who forgot about triggers I'd written just a short while beforehand. It is just like stepping on a rake in the garden that you've just put down. It hurts, but you just feel so foolish.
Thanks, Phil, for the Monday laugh 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 18, 2016 at 6:36 am
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. 😛
April 18, 2016 at 6:37 am
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:
April 18, 2016 at 6:44 am
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.
Manie Verster
Developer
Johannesburg
South Africa
I am happy because I choose to be happy.
I just love my job!!!
April 18, 2016 at 7:10 am
Though we have some, I was was always told they were slow and to try and avoid them.
Viewing 15 posts - 1 through 15 (of 51 total)
You must be logged in to reply to this topic. Login to reply