Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

sql clr + visual basic.net 2005

Posted on 2008-10-13
4
255 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

860 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