update without firing Update trigger 1

[Qik3Coder]

Is it possible to do an update on a table that has an update trigger, without setting off the update trigger?

I have an Update trigger that is supposed to fire when the users update a single record. It freaks out if you hand it a multiple table update. (PS: I didnt write it, but i am not "allowed" to alter it)

I need to update several different combinations of data, and don't just want to alter the table to "return", because it's in live.

Thanks,

SWM Programmer Seeking 3d Modeling work. Either for long term commitment or just friendship. Likes long hours in front of a flat screen with heavy doses of Mountain Dew.

 

[SQLDenis]

-- Disable the trigger.
ALTER TABLE YourTable DISABLE TRIGGER trig1
GO

UPDATE YourTable 
SET......

-- Re-enable the trigger.
ALTER TABLE YourTable  ENABLE TRIGGER trig1
GO

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLSister]

I realize you are in a bad position here, but this trigger must be changed. It is irrespeonsible of your dba to allow a trigger like this since it will only enforce the business rules on the last one of a group of records.This creates dat integrity problems and the trigger must be changed.

The only thing I can thinkof that you can do is, one do the update using a cursor so that only one record at a time is updated. This will take much longer than it should and may cause serious performance issues on your server depending on how many records we are talking about.

Or put the db into single user mode, drop the trigger, do the work (including running aprocess to manaully do whatever it is the trigger does in order to maintain data tintegrity)and the recreate the trigger. Given that they won;t let you change the trigger, I imageine this is out of teh qwuestion too. And it would have to be done during the hours when the system is not in high usage. It must be in single user mode when you do this or others could enter data that would not have the trigger fire while you are making your changes which would also create data integrity problems. As you can see, rewriting teh trigger is actually the best approach all around, but I understand that it may nto be possible. I fear you are stuck with the cursor approach. Make sure to document your objectioins to this appraoch and the loss of efficiency inthe system while you are doing it, so you are covered incase your cursor does bring your system to a screaming halt.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[SQLSister]

If you disable the trigger (Learn something new everyday) make sure you write a process to do what ever the trigger would have done for your data.

Again be careful of using this as I believe it disables the trigger for all users until it is renabled again.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[Qik3Coder]

see, the thing is that they are only allowed to update the table data one row at a time, so it's kind of a moot point. The data is mostly static, and i am updating data not looked at by the update trigger.

Thank you for the disable syntax, wish i had thought to look under disable instead of ignore/turn off.

SWM Programmer Seeking 3d Modeling work. Either for long term commitment or just friendship. Likes long hours in front of a flat screen with heavy doses of Mountain Dew.