Solved

sql clr + visual basic.net 2005

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 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