Another day at the office...
I am still doing the same project that dealt with the paging story. (Click here to refresh you 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 an 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 tirgger 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);
UPDATE REQUESTS SET ...
SQL Trigger on REQUESTS:
ALTER TRIGGER trgLogUpdatesOnRequests on REQUESTS AFTER UPDATE
IF OBJECT_ID('TEMPDB.DBO.#TMP') IS NULL
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.