January 13, 2011 at 9:07 am
We have a large call center that is inserting into our database all the time. We have created an archive process to remove older rows. Sometimes we have to put them back fom the archive into the main database. When we do we do not want to fire the insert triggers since the rows already passed through the trigger when it was created initially. We can't disable the insert trigger since a call center person might insert a row at the time we are adding rows back from the archive and for their row the trigger still needs to fire.
So, how do we turn off the triggers for an INSERT ... SELECT pair. Now We see there is a method using Bulk Insert but that requires the data be in a flat file. Currently it is not in is in the Archive SQL Server Database we created.
Any insight would be appreciated. I would love for there to be a SET command that states SET IGNORETRIGGERS ON, similar to turning off identity creation using the set command, that would be only in affect for that session and not effecting other connections.
Steve
January 13, 2011 at 9:35 am
Can you modify the triggers?
If so, you could probably use User_ID to detect who the transaction is coming from, and ignore inserts that are originated from your login. Or use Host_Name() to detect the source of the connection and ignore transactions from there.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2011 at 9:37 am
Yeah we thought of that but we were trying not to modif code. There are about 20 of them on various tables.
January 13, 2011 at 9:40 am
Take an exclusive lock on the table, disable the trigger, import the data, enable the trigger, unlock the table. Or will that take too long and block too many calls or whatever?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2011 at 9:44 am
Nope there is too much activity and blocking anyone will not fly.
January 13, 2011 at 9:47 am
Anything else I can think of involves basic database refactoring. Like partitioning tables instead of archiving, or even more complex solutions.
I think modifying the triggers is going to be your easiest solution. It also sets you up to be able to do this kind of thing again in the future without further work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2011 at 10:02 am
I would have to agree with G on this as far as modifying the code. That will also give you more knowledge of exactly what is going on and where inside your code which can benefit you in the long run. To modify approx 20 triggers is not THAT much unless its 20 PER table to handle and you have a ton of tables this will let you see if after the first modification your code change returned the results you are expecting. Good luck
DHeath
January 13, 2011 at 10:05 am
I agree, we are DBAs though and I will have to get development to do it Meanwhile we are looking into the BULK INSERT using an exported files.
It is on my MS wish list though to be able to do it using the SET command withing a session.
January 18, 2011 at 7:12 am
Have you added it to MS Connect? http://connect.microsoft.com/
You can suggest new features, report bugs, et al, through there.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 18, 2011 at 7:17 am
GSquared (1/18/2011)
Have you added it to MS Connect? http://connect.microsoft.com/You can suggest new features, report bugs, et al, through there.
Good point. Also they can often suggest alternatives you had not considered, as well as the rest of the community.
January 18, 2011 at 7:18 am
And please post back the link here once you're done so we can vote on it (I've had this issue before and chose the recoding way. I would've loved an alternative).
January 18, 2011 at 7:19 am
Will do today. Thanks for that suggetion.
Steve
January 18, 2011 at 7:26 am
Here's another idea that might require much less coding and QA.
How about copying the DDL of the tables involved and calling tblname_hist.
Then copy the forms in question (or even use the same with a flag).
Then simply call the data up from the hist tables rather than the main tables. That way you don't have to retest the whole system for that deployement.
January 18, 2011 at 9:02 am
here is the suggest on MS Connect.
Being DBA, I don't have control over the code and our developers and already rejected that method. Thanks.
Steve
January 18, 2011 at 9:13 am
Why did they reject that option?
The only big overhead I see is the remember to change schema in 2 places rather than one.
Option z might be to create a db partitioning... Live, archive and twilight in between where you recheck stuff.
That way when you make change scripts, it's quite easy to apply the script in 2 places rather than changing the script. Of course you might need a few more if(exists) or if dbname = ...
Then refactor the selects to do union alls in both dbs instead of only the oltp one. I however have no idea on the perf impact of this solution.
I don't see any way out of this one without some elbow grease. Even if MS ever decides to do your triggeroff solution, you're going to have to wait a few months before getting that release!
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply