Update statement force no trigger

Question

Post reply

Update statement force no trigger

Andrew Deren

Mr or Mrs. 500

Points: 597
More actions
April 29, 2006 at 11:04 am

#112727

Is there a way to do an update on a table that has a trigger, but do an update so that the trigger does not fire?
I know that it defeats the purpose of the trigger, but I have a table that has 100 columns that if any but 3 change I want to fire a trigger, but when I update any of those 3 I don't want that trigger to go on.
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

check out this section of the books online for a solution :

UPDATE, UPDATE (described)

sushila SSC-Dedicated Points: 35293 More actions · Answer 2

You can also get loads of information from the "create trigger" page....more specifically...

"The IF UPDATE (column_name) clause in the definition of a trigger can be used to determine if an INSERT 
or UPDATE statement affected a specific column in the table. The clause evaluates to TRUE whenever the 
column is assigned a value.

IF UPDATE (column)

Tests for an INSERT or UPDATE action to a specified column and is not used with DELETE operations. More 
than one column can be specified. Because the table name is specified in the ON clause, do not include the 
table name before the column name in an IF UPDATE clause. To test for an INSERT or UPDATE action for more 
than one column, specify a separate UPDATE(column) clause following the first one. IF UPDATE will return 
the TRUE value in INSERT actions because the columns have either explicit values or implicit (NULL) values 
inserted."

**ASCII stupid question, get a stupid ANSI !!!**

vadba SSChampion Points: 11133 More actions · Answer 3

Maybe this will help:

ALTER TABLE MyTable DISABLE TRIGGER ALL

-- do you update here

ALTER TABLE MyTable ENABLE TRIGGER ALL

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

The problem is per column update... not by tsql batch.

While it can't be used here it could certainly help him someday.

vadba SSChampion Points: 11133 More actions · Answer 5

Yes..you're correct. It always helps to read the problem carefully!

Andrew Deren Mr or Mrs. 500 Points: 597 More actions · Answer 6

Thanks guys for quick response.

If (Update) will work great for me.