August 16, 2013 at 1:44 pm
I have a project that I am about to get hip deep into, and I want to check if my approach is going to cause more problems than the system currently has.
Project:
We have a batch process that updates a table. This table has a trigger that is over 5,400 lines long, and is a real nightmare. The trigger is really nothing more than a giant Case statement, but all of the updates it makes really need to be completed right away instead of the records being marked with a flag to update later (believe, I argued this point till even I had to accept the trigger has to stay).
I have been tasked with breaking up the trigger into individual sprocs, but some of the steps will update the same table the trigger fires from. The system is currently set to disable the trigger when the trigger updates its own table, but since I will be running these updates in sprocs I will need to manually disable the trigger, run the needed updates, and then re-enable the trigger.
Additional points to consider:
Currently this is a SQL Server 2000 database, but we are converting it to a SQL Server 2008 R2 database (to be completed no more than 4 weeks from today). There is a question of whether we will run it in 80 or 100 compatibility mode, so my solution needs to account for both variations.
My questions:
Has anyone run into an issue using the approach before?
Are there differences in this running in 2k, vs. 08R2 compatibility 80 vs. 08R2 compatibility 100?
What am I not thinking of that may bite me later?
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
August 16, 2013 at 2:53 pm
Instead of constantly disabling/enabling the trigger, you can set RECURSIVE_TRIGGERS off for the db, unless you really need recursion for other triggers. In that case, you can use CONTEXT_INFO to prevent the recursion from actually doing anything.
As to the rest, I'd like to see an example of the trigger code. It's likely it can be simplified. Moving it into procs will slow it down. It should be reasonably easy to work out something than can be done for either SQL 2000 or 2008.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 19, 2013 at 12:08 pm
I had a situation similar to yours with batch processes, tables, and triggers attached to the tables.
1) Batch process updated tables
2) Tables had triggers attached
3) Triggers were only to be used for online updates, not the batch processes.
( #3 is the opposite of your situation but the same principle applies )
The batch process started by setting CONTEXT_INFO() to 'Batch Process'.
Each time the batch process updated a table, the trigger would fire.
The first thing the trigger did was test for CONTEXT_INFO() = 'Batch Process'. If true, the trigger would return without further processing.
You could adapt CONTEXT_INFO to do the same thing in your process. So your batch process would set CONTEXT_INFO(), and then update tables. When the trigger fires, it would check CONTEXT_INFO(), and when the trigger sees it's being called from the batch update process, it would return without further processing.
Using CONTEXT_INFO like this makes disabling and enabling the triggers unecessary, because your check of CONTEXT_INFO() effectively disables the trigger. If you broke the trigger down into stored procedures, the procs will still see the CONTEXT_INFO() originally set by the first batch process.
I have to qualify this by saying I don't know whether CONTEXT_INFO() works in compatibility mode 80, you would need to test that to verify.
_____________________________
Past performance != future results.
All opinions and suggestions are my own, unless they're really good. Then I most likely read them here...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply