Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql clr + visual basic.net 2005

Posted on 2008-10-13
4
Medium Priority
?
261 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
[X]
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
  • 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 2000 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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

715 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