Using a trigger to perform mass-update-safe, specific-field calculations
I ran into this scenario a couple of days ago. Nothing special or complex, but I'd just like to share it with you.
The scenario
You’ve got a table which contains some information and you want to perform calculations based on this information and store them in the same table or in another table.
The calculations must always be up-to-date.
The calculations are performed via a complex User Defined Function which accepts the other fields’ values as parameters.
The table has got thousands of rows and thus you do not want to use a view because it’ll degrade the overall performance.
For the same reason, you do not want to use a calculated field.
For the sake of simplicity, let’s just suppose that we’ve got a Products table that has the following fields:
Id int
prodName varchar (100)
…
(various other product-related fields)
…
prodAvailabilityGrade int
prodQualityClassification char(1)
We want to calculate the possible revenue we would have from every product in our table using a UDF (let’s call it myComplexUDF) which will accept the availability grade and the classification of the product and, based on a complex and time-consuming formula (which may involve lookups to other tables or complex statistical calculations), will produce a number, our estimated revenue for the product.
We are also aware that our table contains thousands of rows, each representing a product.
Let’s define myComplexUDF here:
CREATE FUNCTION dbo.myComplexUDF (prodAvailabilityGrade int, prodClassification char(1))
RETURNS int AS
BEGIN
-- our specific code here
END
For the sake of simplicity, let’s suppose that the results of myComplexUDF will be going into a field on the same table:
prodEstimatedRev int
Now, let’s see: We haven’t got the option to allow real-time calculation of the values, since that would take too much time (we’re talking about thousands of records here!) So our last resort is to use a trigger for our update (and possibly, insert) operations. The trigger should be able to use the UDF to pre-calculate our value each time we update or insert a record.
The first approach
Let’s see a first version of the trigger (Body only):
DECLARE @ID int
DECLARE @QC char(1)
DECLARE @AG int
SELECT
@ID=id
, @ QC = prodQualityClassification
, @AG= prodAvailabilityGrade
FROM
Inserted
UPDATE
Products
SET
prodEstimatedRev = dbo.myComplexUDF (@AG, @QC)
WHERE
Products.ID = @ID
But, with this trigger, we’ve got the following problem:
Product attributes get updated often. Our table has (supposedly) 10s of columns, but we only need to recalculate the value only when prodAvailabilityGrade or prodQualityClassification change, since these are the only fields participating in the UDF.
The second approach
To solve this, we will use the IF UPDATE clause to make the trigger run only if the two fields that interest us are updated. Here goes:
DECLARE @ID int
DECLARE @QC char(1)
DECLARE @AG int
IF UPDATE (prodAvailabilityGrade) OR UPDATE (prodQualityClassification)
BEGIN
SELECT
@ID=id
, @ QC = prodQualityClassification
, @AG= prodAvailabilityGrade
FROM
Inserted
UPDATE
Products
SET
prodEstimatedRev = dbo.myComplexUDF (@AG, @QC)
WHERE
Products.ID = @ID
END
Okay, we solved that. Now, what if we need to do a mass, single-statement update (e.g. UPDATE Products SET prodQualityClassification = ‘A’ WHERE SomeField = ‘SomeValue’)? This would essentially make the trigger run only once and we would not be able to update the corresponding rows with the new values for the prodEstimatedRev field.
The third approach
In order to solve this problem as well, we modify our trigger as following:
DECLARE @ID int
IF UPDATE (prodAvailabilityGrade) OR UPDATE (prodQualityClassification)
BEGIN
UPDATE
Products
SET
prodEstimatedRev = dbo.myComplexUDF (
Inserted.prodAvailabilityGrade
, Inserted.prodQualityClassification)
FROM
Products
INNER JOIN Inserted ON Products.ID = Inserted.ID
END
Conclusion
What we did was to update the Products table based on a join with the Inserted table (which, in the case of a batch update, contains more than one rows). We used the values from the Inserted table as arguments to the UDF and we are now sure that our table is batch-update-safe, and that our trigger will run only in the case that the two fields that interest us are updated.
I hope that helps you solve things quicker when you run into a similar situation.
