Author: way0utwest

I had someone ask me about using triggers to detect changes in their tables. This is a second post looking at triggers, in this case, modifying my trigger to detect more changes and using that information.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

The Setup

We’re using the same table from the last post.  This is the dbo.Customer table with a PK and 5 other fields.

In this case we want to track the changes to an email and capture what those changes are. In other words, if I update the email from ‘sjones@sqlservercentral.com’ to ‘steve.jones@red-gate.com’, I want to capture

• That the Contactemail was updated
• The old value was sjones@sqlservercentral.com
• The new value is steve.jones@red-gate.com.

To do this, let’s modify our trigger. We can still test if the field is updated with UPDATE(). We did this in the last post.

This will let us know that the column changes by returning a boolean. If this is true, we want to insert the new values into our logger. We also want to capture the old value. These values are stored in the inserted and deleted tables, which are available in a trigger. I’ll use a join between these on the PK to get the same data from both.

I’m also using a query with these tables because more than one row can be updated and we want to capture all the changes.

Here is my new trigger, with the OR ALTER added to the code.

CREATE OR ALTER TRIGGER Customer_tru ON dbo.Customer FOR UPDATE
AS
BEGIN
     IF UPDATE(CustomerName)
         INSERT dbo.logger (logdate, logmsg) VALUES (DEFAULT, 'dbo.Customer.CustomerName changed')
     IF UPDATE(AddressKey)
         INSERT dbo.logger (logdate, logmsg) VALUES (DEFAULT, 'dbo.Customer.AddressKey changed')
     IF UPDATE(CustomerStatus)
         INSERT dbo.logger (logdate, logmsg) VALUES (DEFAULT, 'dbo.Customer.CustomerStatus changed')
     IF UPDATE(CustomerContact)
         INSERT dbo.logger (logdate, logmsg) VALUES (DEFAULT, 'dbo.Customer.CustomerContact changed')
     IF UPDATE(ContactEmail)
     BEGIN
         INSERT dbo.logger (logdate, logmsg) 
         SELECT GETDATE(), 'ContactEmail updated from ' + d.ContactEmail + ' to ' + i.ContactEmail 
          FROM inserted i
          INNER JOIN Deleted d ON i.CustomerID = d.CustomerID
     END
END

This is mostly the same code, but now I’ve changed the last conditional test. If the email is updated, I want to query the inserted and deleted tables and create a log message. This is the type of thing I’ve seen often in systems, and while it’s not a great pattern, it does let me capture some information. There are some problems with this, I’ll discuss in the next post.

We can see below that when I run this code, I get the updated captured and logged.

This post has introduced a few new things, the inserted and deleted tables, which I didn’t discuss in the last post. However, they are useful when you want to capture information affected in triggers, which can be more than one row. Using these tables helps you set up triggers that handle multiple changes.

There are problems with this trigger, mainly with NULLs, potential performance, and architecture in what is captured, but we’ll address those in the future.

SQL New Blogger

This post looks at enhancing a previous post and providing more information. You (hopefully) learn from your work, from both feedback  and experiments, and you should modify your thinking and work. This shows how I’ve adapted something I did previously, which is a skill we all need.

This was a 20-30 minute post for me. You could likely do it in a similar amount of time.