May 21, 2008 at 5:48 am
I heared that a good database developer should try to avoid using Triggers.
What is the main reason to avoid Triggers?
and
What is the alternative for Trigger?
😉
May 21, 2008 at 5:52 am
They extend the length of transactions, the increase the chance of deadlocks, and most people don't know how to write one. 😉
Do whatever you need to in the proc before you do the insert/update/delete, rather than writing the logic into a trigger. Especially for validations. Rather validate before trying to change tha data than after.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 21, 2008 at 6:05 am
Vijai,
I think it would depend on the reason for the trigger in the first place?
Matt.
May 21, 2008 at 10:30 am
That's probably a reasonably good starting position - "How can I accomplish this without a trigger?". I would put this right along with "How can I accomplish this without a cursor?" and "How do I get around blocking transactions without using NOLOCK?".
Triggers can be useful and perform well and not cause any issues if written correctly and used in the correct situations.
If you find yourself in a situation in which you think you need a trigger, make sure you have looked for other options first. They tend to be a "simple" bad solution for a lot of situations.
May 21, 2008 at 10:49 am
GilaMonster (5/21/2008)
They extend the length of transactions, the increase the chance of deadlocks, and most people don't know how to write one. 😉Do whatever you need to in the proc before you do the insert/update/delete, rather than writing the logic into a trigger. Especially for validations. Rather validate before trying to change tha data than after.
I agree with almost all Gail has said (as is usual). The only reason I put things in a trigger instead of a proc is to protect me from me. Most triggers I use are "audit" or change history triggers and I want to make sure I catch any ad-hoc changes a developer or dba may make without using the stored procedures. I usually try to avoid that, but I think we all do it sometimes.
I worked with a commercial product that had cursors within triggers and triggers that would only handle a single row. When I contacted them about a performance issue that directly related to a poorly written trigger I just upset them. Oh well, maybe they fixed it in a later release.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 21, 2008 at 10:50 am
Michael Earl (5/21/2008)
That's probably a reasonably good starting position - "How can I accomplish this without a trigger?".
Definitely a good question to ask before relying on a trigger. One case scenario where a trigger may be required is where in-house customization is required on a vendor provided application. I had this need in a few cases with my previous employer and it appears I'm going to need it here when we start looking at using more of our vendor's application. I always look to see if a scheduled stored proc can accomplish what is needed before considering a trigger. Triggers are needed mostly when the effect needs to be immediate so the data is live.
While vendors try to anticipate every possible need their product will be addressing, they will never hit the mark 100% with any business. Our vendor's application is the best for what we do but it still only takes care of 85% (rough figure) of what we need. The rest we have to customize and tweak to get exactly what we need. I have already been told that when we move budgeting from the old application, I'll have to do some substantial customization. I'm hoping to avoid triggers but the previous contractor (my predecessor) indicated that triggers would be necessary to get it to work.
May 21, 2008 at 10:59 am
I have done the same Jack - triggers can bee good for auditing because I usually want them to be part of the original transaction and like them to be transparent. They just have to be written correctly.
Jim - yup we have all had to deal with vendor product that do not do what we want them to. This is often one of those - I have to do it because there is not a better solution situations.
May 21, 2008 at 11:34 am
Jack Corbett (5/21/2008)
The only reason I put things in a trigger instead of a proc is to protect me from me. Most triggers I use are "audit" or change history triggers and I want to make sure I catch any ad-hoc changes a developer or dba may make without using the stored procedures.
That's the main thing I use triggers for as well. Auditing, especially if you have to comply with one or more of the recent laws, must not miss changes, no matter who made then and how.
Oh well, maybe they fixed it in a later release.
And maybe pigs are going to fly...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 23, 2008 at 8:35 pm
Another reason to be cautious with triggers is that any errors or rollbacks that occur in a trigger affect the entire transaction. Therefore, if your trigger is poorly written and relies on things it ought not, you will see transactions rolled back when you really don't want them to.
One reason to use triggers beyond auditing is referential integrity across databases. Foreign key constraints only work within the same database. If you have an application or set of applications that must maintain data across multiple databases, the only way to ensure consistency is to go to a trigger. And I hear what you're saying with respect to the fact that you can code it into the stored procedure. However, folks change stored procedures a whole lot more often than they do triggers. I have seen cases where the data integrity checks were removed out of a well-intentioned developer who just didn't understand the full scale of the system. Had the checks been in a trigger, he wouldn't have touched them.
K. Brian Kelley
@kbriankelley
December 1, 2020 at 11:36 pm
Although this thread is ancient history, a couple more points -
Microsoft's implementation of triggers disallows the modification of data being persisted. So if you wanted a field named date_last_modified on your data table and you wanted to use a trigger to set that value to getDate() via an update trigger, you can't. I made a note some time ago that the MS implementation of triggers forces developers to write things like cursors or to issue additional update statements from within triggers - which makes no sense to me. If you issue an update from within a trigger (against the table being triggered - for example, to set date_last_modified), won't that update re-fire the trigger? Apparently it doesn't - but that's MS for you. With other RDBMSes you can simply catch an update and modify the value at update time if you want to.
I have another note - "It turns out, from discussion on dba.stackexchange, that MS implements triggers as batches of updates at a time - so you can access inserted and deleted as complete rowsets, but not modify values. So for example, to determine whether a value in a column has changed, you have to join inserted and deleted on the primary key. Apparently, if your update is going to update 150,000 rows (as it does with the clean script), then the claused to check whether 'EXISTS' rows in inserted and deleted that join on ID but have different values in the column of interest... results in full table scans of those sets of 150,000 rows. Insane." Where I mention "clean script" - this is invoked when we restore a production backup to another environment such as for testing. This script will modify data values for things like server/connection settings, email addresses and so on. If you have 150,000 rows of client data and each client has an email address and you want your test environment to set that email address to something like testing@ourcompany.org and if you have an audit on that table using triggers, then the above scenario applies and your clean script will die a slow death. Very slow. Or put another way - you now have to modify your clean script to disable the trigger before updating. Whether or not the scenario is valid, it's a point to bear in mind - that updates to large amounts of data on tables with audit triggers can slow things down hugely. For a clean script, maybe you can disable the trigger, but if your production environment has a valid need to update large quantities of data, well, then your trigger may impact performance. I never had this problem with other RDBMSes but per my note, MS seems to handle triggers differently.
December 2, 2020 at 3:17 pm
So if you wanted a field named date_last_modified on your data table and you wanted to use a trigger to set that value to getDate() via an update trigger, you can't.
That's totally incorrect.
It is true that you cannot modify the content of the INSERTED and DELETED tables like you can in other RDBMS's (I actually DO like Oracle's implementation for that) but AFTER triggers in SQL Server CAN update the base tables at any time and, although the implementation of INSTEAD OF triggers has a major suck factor, they can also modify the base tables.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply