October 9, 2007 at 2:17 pm
Hello!
I´m having some issues with a new package I built.
The packages does upserts to a table in my database, and it´s all working fine.
However, I have an update trigger on my table, which I don´t want to fire when running my package.
So I guess I´ll have to disable the trigger when running the package...
Problem is, I still want the trigger to be fired for any updates coming from other sources
than my package.... How can I accomplish this? Is it possible?
October 9, 2007 at 2:36 pm
Use and execute SQL task in your package and disable the trigger prior to your data flow task.
USE pubs
go
ALTER TABLE dbo.employee DISABLE TRIGGER employee_insupd
go
Tommy
Follow @sqlscribeOctober 9, 2007 at 11:18 pm
Make sure that you enable it by using a T-SQL task after the execution of your task
My Blog:
October 9, 2007 at 11:31 pm
What you guys describe is exactly what I´ve already done.
Problem is, what happens if an external application makes an update while my package is running and the trigger is disabled? I still want my trigger to fire for any updates coming from any source other than my package!
I guess it would be possible to somehow lock the table while running my package,
but how would I do that?
October 11, 2007 at 6:40 pm
How about this? In your trigger make sure that it exits immediately if the update is a multi-row update. Then in the package import into a staging table first (a clone of the ultimate destination table but with no tirgger). Then write t-sql to do the insert and/or joined update.
October 11, 2007 at 11:55 pm
Finally solved it by setting an "Application name" for my destination table connection manager.
In the trigger, I then check which application fired the trigger, like so:
IF (SELECT PROGRAM_NAME FROM MASTER.DBO.SYSPROCESSES WHERE SPID=@@SPID) <> 'MYAPPNAME'
BEGIN
....DO WORK
END
This is exactly what I needed!
October 12, 2007 at 5:32 pm
You could shorten that to "IF APP_NAME() != 'MyAppName'", which would probably be better than directly referencing the sysprocesses compatability view.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply