Using Database Triggers to increase information available to report designers
When you first start designing reports, you add tables using a wizard, work out the joins between the tables. Then you’re off. First report designed in no time at all. A few weeks later, you’ve designed all the easy reports. Then you learn to use subreports for that data that just won’t join.
A little while later, you find that Crystal Reports ‘out of the box’ can’t provide the report you need. Then you start to use custom SQL, write a database view or a stored procedure.
What do you do if the data you need isn’t in the database?
This happened to me last week. New customer has an application that tracks support calls. Each call is assigned to an engineer, gradually progressing through various statuses until it’s completed. All we needed to know was how long each call had been in its current status.
Easy! There’s a ‘last update’ column. Sadly, there are more ways to update a record than just changing the status. Each time a new note was entered for a call, the ‘last update’ date would be changed.
We looked at all the other dates in the database to see if there was any way to identify when the status was changed. Nothing at all. Time to look at a different solution.
What’s a Trigger?
A trigger is just a piece of SQL that’s executed when something else happens. For our purposes, it’s executed when a record in a specific table is either inserted or updated.
What next?
We’ve identified that we need to know when the status column of a record is changed. We’ll also need to know when it’s created. We also need somewhere to store this information, so lets create a new table:
Nice and simple. pm_guid is the primary key of the table we’re extending. LastUpdate is the date and time it was last updated and LastStatus is the most recent status.
We need to pre-populate this with the existing records to be sure we capture existing calls. Easily done with some SQL:
insert into zLastUpdate (pm_guid, LastUpdate, LastStatus) (SELECT pm_guid, isnull(pm_last_update, ‘2000-1-1 00:00:00′), pm_status_guid from pm_process)
The isNull was required just to make comparisons simpler in the trigger. IsNull is just a shortened version of the Coalesce function. I just find it easier to read.
Now we come to the main attraction, the triggers. We need one to detect Inserts and another to detect Updates:
CREATE TRIGGER tr_pm_process_I on pm_process AFTER INSERT AS
INSERT INTO zLastUpdate (pm_guid, LastUpdate, LastStatus) (SELECT pm_guid, isnull(pm_last_update, ‘2000-1-1 00:00:00′), pm_status_guid FROM inserted)
GO
CREATE TRIGGER tr_pm_process_U on pm_process AFTER UPDATE AS
IF UPDATE (pm_last_update)
BEGIN
UPDATE zLastUpdate SET LastUpdate = inserted.pm_last_update
FROM zLastUpdate INNER JOIN inserted ON zLastUpdate.pm_guid = inserted.pm_guid
WHERE zLastUpdate.LastUpdate < inserted.pm_last_update and zLastUpdate.LastStatus <> inserted.pm_status_guid
END
One thing to note about a trigger is that SQL creates an internal table for the trigger called ‘inserted’. This contains the new row so that we can easily access the correct data.
If you look at the first trigger, it simply inserts the new record into our extra table. There’s a check for a null date, but other than that, it’s a straight insert. The reason we use SELECT in the INSERT instead of VALUES is that we can’t be certain there is only one record in the inserted table. The trigger runs once per batch, so there could be multiple records in the batch.
The Update trigger is more interesting. We check that it is the ‘last update’ column that’s change, then check that the status has changed before updating our record.
It’s now simply a matter of joining our new table to the call record table and we can now use our new date column. All achieved without affecting the source application.
Caveats
There’s no such thing as a free lunch! Each time the trigger executes, your SQL Server is having to do a little more work than previously. In this example, it’s not much, but in a server managing thousands of concurrent requests it may cause a problem. Be careful using this on tables that are heavily used and frequently updated. Be ready to disable the trigger if performance is affected. You drop a trigger in the same way that you drop and index or a table.
