Using SQL Server Triggers to Save ColdFusion
Currently, I'm working on a large application that involves a Flex UI backed by ColdFusion 8 and SQL Server 2008. The ColdFusion code consists of a large base of CFCs that run many queries (via cfquery) for all database interaction/communication (CRUD, advanced searchers, etc.). The CFCs are invoked as Remote Objects from the Flex UI. In most cases, the CFCs return large arrays of objects for Flex to use in the UI.
Since ColdFusion 8 is particularly slow at object instantiation, my concerns about the application's performance under load are great. While getting acquainted with the existing code base and project, I was tasked to create a routine that enabled an application user to rate objects in the UI and compute an aggregate ranking for these objects based on rankings from all application users. As I worked through what steps I needed to take to accomplish this, I realized that a triggers was just what I needed (it helped that I was scanning a SQL Server book at the time!).
It turns out that triggers are not difficult to write and implement and, ultimately, can really help boost ColdFusion's performance in the right situations. Thus, this exciting blog entry :).
Triggers
Many already know this but, since I didn't fully 'get' triggers, I thought a brief introduction or definition would be good. Triggers are routines that run once a specified event occurs in the database. They're great for enforcing referential integrity (i.e., ensuring you don't delete a row from a table without also deleting related foreign key entries) and minimizing work on the application server by running a range of tasks in the DB itself; rather than your web application server.
There are a two main types of triggers in SQL Server (AFTER and INSTEAD OF) but, thus far, I've only gotten into the standard trigger (AFTER). Essentially, an AFTER trigger is an event listener that, when fired, tells SQL Server to run the compiled code of your trigger.
But when does it fire? Well, you get to tell it when to fire. Typically, an AFTER trigger is fired immediately following a successful insert, update or delete on the watched table
The Problem
As I mentioned above, the current application on which I'm working has a feature that enables application users to rank objects from 0 to 10. The ranking is handled in the UI (quite nicely -- we have awesome designers and interactive folks) and, when the user has finished, s/he saves their rankings and the data is submitted to a ColdFusion via a remote object in Flex.
Where it gets 'tricky' is that with each set of rankings saved, we need to immediately update our aggregated ranking data and return those to the UI.
If I were to do this in ColdFusion alone (using CFC methods with CFQUERY tags), the workflow might be something along the lines of the following:
- Save the object's rankings/ratings to the database
- Grab all previous (plus the current) rankings for the current object
- Insert the updated aggregated ranking data in the database
- Query the database for the aggregated rankings for all objects and return to the UI
Even though this isn't a complex, uncommon, or intensive operation, it still takes up to 4 database queries.
The Solution
My solution incorporated a stored procedure and the trigger and only 1 step for ColdFusion. Here's the flow as far as ColdFusion is concerned:
- Save the object rankings/ratings to the database (CFC method invokes a stored procedure on the DB server)
- Stored procedure inserts, updates, or deletes some ranking data and my trigger is then fired
- Aggregated rankings for all objects returned from the stored procedure and are then sent back to the UI
The Trigger
Below, the trigger I created is listening for a product_sales table to be affected in some manner (insert, update, or delete). When one of these events occur, I need to update the aggregate rank for the product that was just sold.
Our aggregate rank is calculated quite simply: add the total customer rankings for a product and divide by the number of times it has been ranked. Once calculated, we need to update another table that holds the aggregate product rankings
*Note: I had to alter my actual trigger, so the tables referenced in the example were just pulled together for the example itself (and not intended to represent an ideal or good DB design).
Following is the T-SQL for the trigger:
CREATE TRIGGER tr_my_trigger ON [dbo].[product_sales] -- we're listening for any changes to the table noted above AFTER INSERT,UPDATE AS BEGIN -- Insert statements for trigger here declare @prodid nvarchar(50), @aggregate_ranking numeric(18,0) select @prodid = prod_id from inserted select @aggregate_ranking = (sum(rank) / count(prod_id)) from product_customer_rankings where prod_id = @prodid update product_ranks set aggregate_ranking = @aggregate_ranking where prod_id = @prodid END
First, we declare a trigger on the table we want to watch. We specifically created an 'AFTER' trigger for an INSERT or UPDATE event occurs on the product_sales table.
Second, we declare two local variables that the trigger needs (@prodid and @aggregate_ranking).
Third, we set the value for our first local variable, @prodid. I think this is where the trigger gets cool.
In order to successfully update the aggregated ranking data, we need to get the id of the product that was just sold. Notice the line "select @prod_id = prod_id from inserted".
We can access the modified data from a special table called 'inserted' (if your routine did a delete, you would select from 'deleted' and not inserted). So, this table holds the details of the product that was just sold and whose sales data was added to the product_sales table milliseconds earlier. With that simple select statement, I now have the product id I need to do my real work.
Fourth, we set the value for our second local variable, @aggregate_ranking. The value of @aggregate_ranking is set to the result of the SQL statement that follows (using our local @prodid variable to get the right data).
Finally, we update the aggregate ranking for this product in the product ranks table.
Perhaps I'm overly excited about what potential this holds ... I've (clearly) only scratched the surface of what you can do with triggers to keep some of the workload away from your application server (be it ColdFusion, .NET, PHP, whatever) but the possibilities seem vast.
