Click here to Skip to main content
13,505,369 members
Click here to Skip to main content
Add your own
alternative version

Tagged as


5 bookmarked
Posted 7 Apr 2009

Trigger: Thou Shall Fire at my Command

, 7 Apr 2009
Rate this:
Please Sign up or sign in to vote.
How to make your t-sql trigger respond differently depending upon the connection

Another day at the office...

I am still doing the same project that dealt with the paging story. (Click here to refresh your memory). We still have not got the application into production yet. I last blogged about it 6 months back! The reason why this is so...I can't really recall. However, I had given the web site for 1st round of testing. I got the feedback too. I had worked on most of the corrections. But there was one thing that seemed like it would take forever. The scenario was:

You have a request. It has a specific completion time. If that request was not able to be completed in time, it would get escalated. Meaning we'd all receive a mail stating that this particular request needs attention. For this, we had a SQL Server job scheduled in the backend. The code includes a call to vbscript; the vbscript makes a call to a web service which does the mailing part. How to get the script call the web service was another story. The code also had to do update a particular field on a table (where all the requests lie); that table had a trigger. That field was a sort of flag which denoted that the request was escalated.

I had written an update trigger for that table before. This trigger would fire everytime there was a status update. This update may be an indication that this request had been completed at so and so time, by so and so, etc., or that it was rejected, accepted by someone, etc. The trigger's main job was to log all the updates. But, this trigger would run even if I simply updated one field on that table (the requests table).

So here you have it. A scenario where you would want a trigger to work differently depending on the circumstance.

At first, I thought a simple alter table would do the job. It would, but, I would disable the trigger for everyone. This had to happen at a specific instance only, and still I would not want the trigger to be disabled in any case. I had posted the query in a usenet discussion group. And I got a response too. Something really clever, and it makes use of temporary tables too.

Temporary tables only exist for that connection it was created in. You can try this in your query analyzer window if you must. Create a table with a '#' prefixed in front of it. And then open a new connection, try selecting the temporary table. You'd most likely receive an error message saying that the object doesn't exist. So how did we utilize this fact?

Just before I did the update on my requests table, I create this temporary table; and in my trigger, I do an object_id search for it. Something like the following:

SQL Job Script Snippet

CREATE TABLE #tmp (i int);


SQL Trigger on REQUESTS:
        -- Execute the normal trigger logic
        -- Execute conditional logic here.

It is a quirky way. I was wondering if there was a way to know which instance was running or who was executing the code, etc. You can even write another logic based on finding out who is running the code. I think this logic should be clean at least for my case.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Software Developer (Senior) iGATE Global Solutions
India India
No Biography provided

You may also be interested in...


Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.180417.1 | Last Updated 7 Apr 2009
Article Copyright 2009 by deostroll
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid