March 26, 2015 at 12:24 pm
Hello, I seem to remember that several years ago, triggers were considered bad practice. So does anyone here still use triggers? I unfortunately have to make some changes to an app that uses/requires them.
March 26, 2015 at 12:35 pm
Triggers are just like a number of other things some people call bad. They are not bad, but can be a problem if not used correctly.
What do your triggers do?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
March 26, 2015 at 2:00 pm
Triggers aren't bad practice per se. Some people write horribly inefficient triggers, which would cause problems. Triggers must be written for efficiency, but they are a great tool when used properly.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 26, 2015 at 2:33 pm
I don't think there is anything wrong with Triggers when used for the right reasons and used correctly. I have seen people do bad things with triggers.
Dwain Camps recently wrote an informative article about DML triggers which is worth a read:
-- Itzik Ben-Gan 2001
March 26, 2015 at 3:42 pm
Piling on.
Triggers are not bad. They just have a hidden behavior that, when they're written poorly, can lead to lots of issues and long troubleshooting sessions. Use appropriately, they're fine.
Now, multi-statement, table-valued, user-defined functions on the other hand, those things are all things evil.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 26, 2015 at 4:11 pm
Grant Fritchey (3/26/2015)
Piling on....
Now, multi-statement, table-valued, user-defined functions on the other hand, those things are all things evil.
You mean in a trigger? 😀
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
March 26, 2015 at 4:23 pm
Alvin Ramard (3/26/2015)
Grant Fritchey (3/26/2015)
Piling on....
Now, multi-statement, table-valued, user-defined functions on the other hand, those things are all things evil.
You mean in a trigger? 😀
I suspect he meant that they are evil anywhere.
-- Itzik Ben-Gan 2001
March 27, 2015 at 6:19 am
Alan.B (3/26/2015)
Alvin Ramard (3/26/2015)
Grant Fritchey (3/26/2015)
Piling on....
Now, multi-statement, table-valued, user-defined functions on the other hand, those things are all things evil.
You mean in a trigger? 😀
I suspect he meant that they are evil anywhere.
This.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 27, 2015 at 7:14 am
Adding to the pile. Triggers themselves aren't bad, only poorly-written ones. Functions themselves aren't bad, only poorly-written ones. One example of bad is what Grant said. They can suck the life and performance out of the most well-written query.
March 27, 2015 at 7:42 am
We have triggers to capture the changed data of a table.
March 27, 2015 at 8:07 am
We have some here, and they are widely used. The biggest problems, as pointed out, are poorly written triggers. There are also issues with fundamental design problems in how people use triggers. They fire once per transaction in SQL Server, once per row in other platforms. That means you must tailor triggers to the platform. You must also understand they fire every time. No exceptions. This leads to issues when people use triggers to enforce an action, but they need an exception at times.
Triggers are fine, but they are like sharp edges. They can be tools, or very dangerous. You should know how to use them.
March 27, 2015 at 9:39 am
To add one more of the same opinion: triggers have a place, usually for data auditing. I've also used them to enforce a low-level type of referential integrity that can't be enforced through standard methods (for example, a job is inactivated, firing a trigger that inactivates all the children). They must be written to accomodate set-based queries. If it can only handle a change caused by a single row, then it should be re-written.
March 27, 2015 at 4:26 pm
When used properly and well written, triggers are extremely useful; when used inappropiately they are awful; when written badly they are awful; when they are both used inappropriately and written badly they are an out and out disaster - - and one of the sad things about the world is that triggers (like most other demanding tools) have a horrible large number of people who think they know what they are doing with them but can neither sensibly judge when to use them nor write them competently - and those people have of course given triggers a terrible reputation.
Tom
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply