Trigger: Thou Shall Fire at my Command

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 1^st 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);

UPDATE REQUESTS SET ...

SQL Trigger on REQUESTS:
ALTER TRIGGER trgLogUpdatesOnRequests on REQUESTS AFTER UPDATE
AS
BEGIN
    IF OBJECT_ID('TEMPDB.DBO.#TMP') IS NULL
    BEGIN
        -- Execute the normal trigger logic
    END
    ELSE
    BEGIN
        -- Execute conditional logic here.
    END
END

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.