Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ITTrucker
Yak Posting Veteran
64 Posts |
 Posted - 2013-10-07 : 09:52:45
|
| Is it better to DISABLE a TRIGGER in a separate job step and then enable it at the end of the job in it's own step, or DISABLE and ENABLE on each step? Just wondering what the best practice is.I have a SQL job that updates a table with 3 steps. A software update just added a trigger on that table that is preventing me from updating it through the back end without disabling the trigger. I can run anALTER TABLE [tablename] DISABLE TRIGGER [triggername]UPDATE [tablename] SET [column] = valueALTER TABLE [tablename] ENABLE TRIGGER [triggername]on each step, or I could put the DISABLE as step 1 and then run my updates and then put the ENABLE on step 5, or the DISABLE and the 1st update on step 1, and then the last update followed by the ENABLE in step 3, or something else I'm not thinking of. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-07 : 10:14:18
|
| It is very unusual to disable a trigger, do DML operations and then enable the trigger. Something about that does not sound right. For example, you may be disabling the trigger to allow a controlled set of data that you want to insert, but then another process may be running which may not be as controlled as your process, which can destroy the data integrity.If you don't want the triggers to fire and if you are using bulk insert, there is fire_triggers option that you can turn off. Alternatively, if you have the ability to do so, examine the logic in the trigger and allow for the specific condition that you want to handle.If you must disable and then re-enable the trigger, it really does not matter which way you do it. I would try to keep the interval for which the trigger is disabled to a minimum. |
 |
|
|
ITTrucker
Yak Posting Veteran
64 Posts |
Posted - 2013-10-07 : 12:19:06
|
| The trigger is just updating an audit table, it's not doing any data validation, it's just inserting a record into an audit table.The trigger is grabbing the data from the inserted and deleted tables and then giving a "String or binary data would be truncated" error. The software company doesn't use consistent field names, so I'm not sure which field is causing the error. Couldn't get a trace that showed the results of the select, and couldn't debug it in detail, so I went through field by field and found the field that had the problem and added a RIGHT(fieldname,20) to truncate the string to match the table. Then about 2 minutes after I fixed it, the software company called and looked at everything and then said that the trigger is disabled in their newest version because it was having problems.Thanks for your help. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-07 : 12:53:42
|
I am sure the said software company is glad you didn't post their name  |
|
|
|
|
|
|
|
|
ALTER TABLE in SQL Job