How to Disable a SQL Server Trigger Action

[José António Silva]

Hello,

The following applies to VBA with ADO and SQL Server 2000

I want to use a trigger to manage the deletion of records in a table.
However, I have a procedure in VBA that creates a new connection with the
server, which can be run by any user, and where I don’t want the trigger to
fire.

I investigated the use of SET CONTEXT_INFO but I cannot understand if I can
set the respective value from VBA. In this case I will insert a condition in
the trigger to test the existence of a certain value.

I also investigated the creation of a temporary table in VBA but I am
convicted that trigger cannot find this table because of an out of scope
problem. In this case I will insert a condition in the trigger to test for
the existence of that table.

I hope somebody has an elegant solution for this,
Thank you,

José António Silva

 

[Alex Dybenko]

Hi,
you can un SET CONTEXT_INFO with ADO. normally I write a SP to set
CONTEXT_INFO, say SP_SC, then you can add a call to it before your delete
statement:

con.execute "SP_SC 'some info';Delete from mytable;SP_SC '' "

and then in trigger I read CONTEXT_INFO (see samples in SQL server BOL) and
decide if I run it or no.

Also you can create a table, with some flag, then you can set this flag, run
delete and reset it. this is much more easy, also to read it in trigger.

 

[José António Silva]

Thanks Alex,

I’m quite decided to create a temporary table to solve this problem.
However, I’m not sure how to test for the existence of a temporary table in
a trigger.
I’m doing the following:

IF EXISTS (SELECT name FROM sysobjects
WHERE name = '#tmp' ) SET Flag=1

but it seems to be not working.

José António Silva

 

[Alex Dybenko]

Hi,
why do you use temp table? you can just make a normal table, and set/rest a
flag in it

 

[José António Silva]

I want to use a temporary table because I want the trigger to do nothing just
in one connection – the connection that previously created the temporary
table. Because the temporary table is not visible outside the connection that
creates it, in other connections the trigger will fire normally.
I can successfully select a value, let’s say a flag, from the temporary
table and from code in the trigger. However I cannot use this procedure for
nothing because when the temporary table doesn’t exist I get a run-time
error. So, I really have to test for the existence of the temporary table.

José António Silva