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:
[code lang='sql']
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
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- 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
[/code]
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.
Anyway, enough rambling! Thanks, again, for reading the post/blog, let along bookmarking it ;)!
Apache Derby: http://db.apache.org/derby/
Derby is the "new" Adobe CF default DB that you mentioned in your most recent comment.
Access is such a different beast, eh? When I was on Windows, I often encountered some Access odd-ness.
One thing that might be worth exploring is to use Apache Derby first and then migrate to MySQL when ready. I've read some good things on Derby and it might match better with MySQL in terms of features, etc. Anyway, just a thought!