How to Disable a SQL Server Trigger Action

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
Hi,
why do you use temp table? you can just make a normal table, and set/rest a
flag in it
 
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
 
Back
Top