Solved

sql clr + visual basic.net 2005

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

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 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.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

831 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