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.
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)
CREATE TRIGGER tr_pm_process_U on pm_process AFTER UPDATE AS
IF UPDATE (pm_last_update)
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
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.
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.