 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / September 2005How to Disable a SQL Server Trigger Action
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.  SignatureAlex Dybenko (MVP) http://alexdyb.blogspot.com http://www.PointLtd.com "Jose Antonio Silva" <JosAntnioSilva@discussions.microsoft.com> wrote in message news:281FFB46-2E53-45C1-B27B-EF5C4119C154@microsoft.com... > Hello, > [quoted text clipped - 21 lines] > > Jose Antonio 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 > Hi, > you can un SET CONTEXT_INFO with ADO. normally I write a SP to set [quoted text clipped - 34 lines] > > > > Jose Antonio Silva Hi, why do you use temp table? you can just make a normal table, and set/rest a flag in it SignatureAlex Dybenko (MVP) http://alexdyb.blogspot.com http://www.PointLtd.com "Jose Antonio Silva" <JosAntnioSilva@discussions.microsoft.com> wrote in message news:878F6337-4D31-470D-97DC-6A386344ACFD@microsoft.com... > Thanks Alex, > [quoted text clipped - 56 lines] >> > >> > Jose Antonio 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 > Hi, > why do you use temp table? you can just make a normal table, and set/rest a [quoted text clipped - 60 lines] > >> > > >> > Jose Antonio Silva |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.