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 |
|
anolis
Starting Member
14 Posts |
 Posted - 2004-05-14 : 13:52:56
|
Hi, Listen, I've got this table with data of about 100.000 users. I want to do some cleaning: the zipp code should be in some uniform format, names should start with an Uppercase, etc.Now this table has an update trigger with a lot of actions. I wrote some TSQL, with a cursor and a Fetch-loop, that updates all rows after "reshaping" the data. The whole proces takes about 25 minutes.In this time the tables functionality should not be disabled (so the trigger must stay active), however, I do not want the triggers to fire when I update rows from my batch.Is there something like UPDATE NOTRIGGERS ... etc. Anolis,Developer,MCAD for .NET |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-14 : 14:02:24
|
| You can disable the trigger. Take a look at ALTER TABLE (DISABLE TRIGGER) in SQL Server Books Online for details.Tara |
 |
|
|
anolis
Starting Member
14 Posts |
Posted - 2004-05-14 : 14:14:17
|
| Thanks, but I don't want the trigger to be disabled.It should only be disabled in my batch, not for other processes that use the table in the mean time.Anolis,Developer,MCAD for .NET |
|
|
|
Pat Phelan
Posting Yak Master
187 Posts |
Posted - 2004-05-14 : 15:38:55
|
Add a new user to your database, with a descriptive name like noTrigger. At the beginning of all the "interesting" triggers, add the code:IF 'noTrigger' = user_name() THEN RETURN So that the trigger will "suicide" as soon as it starts. You'll take the trigger launch performance hit (very small), but the bulk of the trigger won't run.You can then use the SETUSER command to "short-circuit" triggers at a whim.-PatP |
|
|
|
anolis
Starting Member
14 Posts |
Posted - 2004-05-14 : 16:54:35
|
| Thanks!That is a very clever solution. This can be used in many ways!Anolis,Developer,MCAD for .NET |
|
|
|
|
|
|
|
|
Update within cursor without triggers