CODEWITHSUNDEEP
sqlsql-servertriggers
aSystemOverload
aSystemOverload

Reputation: 3074

How can you `UPDATE` an SQL Server table without triggering the UPDATE trigger

I have an update trigger that updates some important status fields in a table tblCurrent.
When I first upload the daily batch of records into tblCurrent (circa 10K records), I perform some UPDATEs via three separate stored procedures when these are first uploaded and only then. How can I prevent the update trigger from running during these three initial UPDATEs?

Upvotes: 7

Views: 20452

Answers (3)

JupiterN
JupiterN

Reputation: 143

You could use session variable Context_Info. Before the update set context_info to some value and then test it inside the trigger

-- update code SET Context_Info 0x55555 Update Table set ...

-- Code Inside the trigger SELECT @Cinfo = Context_Info() IF @Cinfo <> 0x55555 BEGIN ... END

Upvotes: 0

Ivan Korneliuk
Ivan Korneliuk

Reputation: 349

You could temporary disable triggers for the table and then enable them back. MSDN article.

DISABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL } ON { object_name | DATABASE | ALL SERVER } [ ; ]

ENABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL } ON { object_name | DATABASE | ALL SERVER } [ ; ]

For example to disable all triggers for given table run following statement:

DISABLE TRIGGER ALL ON tblCurrent;

Upvotes: 8

to StackOverflow
to StackOverflow

Reputation: 124696

One way to do this would be to have some data in tblCurrent that enables you to detect the situation you describe as "first uploaded". For example, a BIT column "FirstUploaded", or a column that is NULL when first uploaded, or even a BIT column "DontFireTrigger".

Then write your trigger to detect this condition, and conditionally update the status fields.

Admittedly this looks like a nasty hack, but perhaps no worse than other solutions.

Upvotes: 4

Related Questions