Solved

sql clr + visual basic.net 2005

Posted on 2008-10-13
4
252 Views
Last Modified: 2013-11-26
I have created a database project in visual basic 2005 basically stored procedures in visual basic (CLR) for sql server 2005. Well this clr is part of a job that runs every 2 minutes on the sql server and inserts rows into a table. Yes we can do this all in sql but there are reasons why we went the database project in visual basic 2005 route. Well to make a long story sort there is a trigger on this table that runs everytime a record is inserted. The clr is inserting a particular value in the table so when this trigger runs it sees this value and immediately exits/returns. Well my question is can I disable this trigger from the clr in visual basic when it is inserting the record and enable the trigger when the insert is done. Basically we do not need the trigger when it is coming from the clr we just need it when an client application is inserting a record into the table. Any suggestions would be greatly appreciated.
0
Comment
Question by:lothos123452000
  • 2
  • 2
4 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22706239
Add a column to the table that has the trigger on it -- something like "AddedByCLR"

Make your CLR procedure set that as true when it inserts.  Leave other access unchanged.  

In the trigger, check for that flag before doing the work.

There may be a more elegant solution ... but that should work.
0
 

Author Comment

by:lothos123452000
ID: 22721297
We are already doing something similiar to that but I was just wondering if there is something I could in the clr that will stop the trigger from even attempting (no check required)? Is that currently even possible?
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 22721504
You can do an ALTER TABLE MyTble DISABLE TRIGGER MyTrigger
then do the corresponding ALTER TABLE to enable the trigger.

The problem is that disables the trigger entirely during that time period -- not just for your particular process.

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=2216785&SiteID=1
That discusses another means of skipping for just your process -- but it also involves a modification to the trigger.
0
 

Author Closing Comment

by:lothos123452000
ID: 31505666
Thanks for the help.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now