Voice of the DBA

I had someone ask me about using triggers to detect changes in their tables. As I explained a few things, I thought this would make a nice series, so I’ve written a few posts on triggers that can be useful. This one looks at detecting a change to a column in a trigger.

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

The Setup

I’ve got a Customer table that I want to use, which doesn’t have any triggers on it. Here is the schema.

CREATE TABLE [dbo].[Customer](
     [CustomerID] [int] NOT NULL,
     [CustomerName] [varchar](200) NOT NULL,
     [AddressKey] [int] NULL,
     [CustomerStatus] [int] NULL,
     [CustomerContact] [varchar](100) NULL,
     [ContactEmail] [varchar](100) NULL,
  CONSTRAINT [CustomerPK] PRIMARY KEY CLUSTERED 
(
     [CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

I also have a logging table where I can store messages from a trigger, which is better than direct output.

Let’s create a trigger. I’ll use an UPDATE trigger here to check if a column is changed and then insert a logging message.

Note: I’m not trying to be efficient here, just create an action based on what changed. THIS IS NOT PRODUCTION QUALITY CODE.

Here is a basic trigger using the UPDATE() function to check if a column changed. I am inserting into the logger table when I detect a change, but in a real application, I’d likely have some business logic here instead of the insert.

CREATE 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)
         INSERT dbo.logger (logdate, logmsg) VALUES (DEFAULT, 'dbo.Customer.ContactEmail changed')
END

Now, I’ll check the time, then run an update, and select from the logger table. This gives me a nice easy way to see what changes were logged. First, let’s update one field.

We can see that the change was logged. I could add more info, but this is just a check of what happened.

Let’s check two changes. We can see that both are detected below.

Now I’ll update all the fields in a row. There are five other than the PK and I can see all changes logged.

Note that I’m just detecting an update to this field and marking it as changed. If the value were set to the same value, which some apps do, this is still noted as an update by this trigger. To determine if this value was actually changed, I’d need to compare the inserted and deleted rows.

That’s a quick look at detecting which fields have changed. In the next post, we’ll look at more information in the trigger.

SQL New Blogger

Getting started blogging can be hard. I’m sure many of you have used a trigger. Why not write about setting up a trigger, perhaps with some logic like this. Have you used updated()? Tell us how and why.

This is a great way to showcase how you approach a problem. This is a basic post here, but I’ve shown above I could do something business related instead of the insert to the logger table.

This was a 15-20 minute post.