The Table Trigger
As I mentioned, this solution is based on a trigger whose main task is to create the job, which calls a few system stored procedures all wrapped inside one stored procedure. Listing 1 shows the template code for creating the trigger. The trigger first calls the sp_CacheExpiryQueue_Insert stored procedure from Listing 2 to add to the CacheExpiryQueue table the name of the table that's being updated. The CacheExpiryQueue table lets the job know which tables' caches it needs to invalidate. As soon as the job invalidates the cache, the job deletes the table-name entry from this table.
The second stored procedure the trigger calls is sp_Create_Process_CacheExpiryQueue_Job. This stored procedure wraps all the system stored procedures you need to create and schedule a job. Listing 2 shows the code for sp_Create_Process_CacheExpiryQueue_Job, which has five steps. First, it creates a unique job name. Next, it creates the job in the msdb database, flagging the job to delete itself if it succeeds. Third, it adds a step for the job, designating that the step will use T-SQL to execute the sp_Process_CacheExpiryQueue stored procedure. Fourth, it calculates the date 10 seconds from the current system time, casts it as an integer, then stores this value in a variable. Finally, the procedure schedules the job to run only once, with a start time of the previously calculated integer variable value.
After it creates the job, the trigger's work is done. Ten seconds later, the SQLServerAgent service executes the job that the trigger created. This means that the job's thread (not the trigger's thread) executes the sp_Process_CacheExpiryQueue stored procedure when the job runs.
The sp_Process_CacheExpiryQueue stored procedure loops through all the table names in the CacheExpiryQueue table, calls sp_Expire_Cache to make the HTTP call to the Web server for each one, then deletes the table-name entry from the CacheExpiryQueue table. Listing 3 shows how to code all this without using a cursor.
To get around having to use a cursor, I simply create a table variable that has an IDENTITY column that starts at 1 and increments by 1. This technique lets you return a known sequence of all the values in the CacheExpiryQueue table. At callout A, the code starts a While loop where the counter variable is less than or equal to the count of records in the table variable. To return the table name, the code queries the table variable where the counter value is equal to the IDENTITY column value. Next, the code at callout B passes the table name as a parameter to the sp_Expire_Cache stored procedure. The code at callout C then increments the counter by 1 after every row in the table variable is processed. If you skip this important step of incrementing the counter, the SQL Server thread will spin forever in an infinite loop.
The sp_Expire_Cache stored procedure, which Listing 4 shows, is responsible for expiring all the Web-application data caches associated with the table name that the code at callout B in Listing 3 passed in to it. The procedure first finds the Web data caches that need invalidating by querying and joining the CacheExpiryUrl and CacheDependency tables. The sp_Expire_Cache stored procedure then puts the results of this query into a table variable that lets it loop through the results in much the same way as the looping code in sp_Process_CacheExpiryQueue does. During the loop, the MSXML2.ServerXMLHTTP COM object requests the cache expiration URL from the Web server. This object is easy to use and thread-safe (that is, you can call it from multiple threads without unwanted interaction between the threads), and it makes the code easy to deploy because you don't need to create any new extended stored procedures to make HTTP calls. You call this COM object by using the sp_OACreate, sp_OAGetErrorInfo, and sp_OAMethod system stored procedures. To execute these stored procedures, the calling user must either be a member of the sysadmin fixed server role or have explicit EXECUTE permissions on the stored procedures. Because you're executing this job in the context of the SQLServerAgent service, you just need to make sure that the account that the SQLServerAgent service is running under is a member of the sysadmin fixed server role or that it has EXECUTE permissions on these stored procedures.
At callout A in Listing 4, the stored procedure creates the COM object before the loop so that you can reuse the same COM object instance for all items in the loop. Calling the sp_OACreate system stored procedure creates the object. To do this, the code at callout A passes in the programmatic identifier (ProgID) parameter MSXML2.ServerXMLHTTP. This stored procedure has an integer type OUTPUT variable (@output), which the code uses as the reference pointer to calling the object's methods. The procedure then uses the sp_OAMethod system stored procedure to call the COM object's open and send methods so that it can make the HTTP request to the Web server defined in the URL. After sending the request, the code checks the HTTP status returned from the Web server. If the status is 200, the request was successful. After every call to the COM object, including its creation, you must check the HRESULT value to see whether an error occurred. If the value is anything other than 0, an error has occurred. In the case of an error, the code at callout B calls the sp_OAGetErrorInfo system stored procedure to retrieve the error information. The procedure then sends the error information to the sp_CacheExpiryError_Insert stored procedure, which adds an entry to the CacheExpiryError table. You don't need to explicitly destroy the reference to the COM object; SQL Server will automatically destroy it at the end of the stored procedure.
As I mentioned earlier, you could implement this design on other relational databases besides SQL Server. As long as your RDBMS supports triggers and some mechanism to make HTTP requests, you can at least implement the design synchronously. If your database system supports programmatically creating and scheduling jobs, you can implement the design asynchronously.
Prev. page
1
[2]
3
next page 
