Tag: T-SQL

I had someone ask me about using triggers to detect changes in their tables. This is the third post in the series. The first one

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 first post.  This is the dbo.Customer table with a PK and 5 other fields. Here is the data in the table:

I had this trigger in the last post, and showed how it captured updates to the ContactEmail field.

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 seemed to work, but did it really?

The Problem

Let’s illustrate the big problem with this change. I’ll run this code:

UPDATE dbo.Customer
  SET ContactEmail = ‘andy@sqlservercentral.com’
  WHERE CustomerID = 2;

If I do this, here are the results:

I get a NULL? Why, the original value is null and when I concatenate null with other values, I get NULL. Not ideal.

Let’s fix this problem. I’ll use a function to handle null values. Note, I need to do this for both the inserted and deleted tables. Here’s the new trigger.

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 ' + COALESCE(d.ContactEmail, 'null') + ' to ' + COALESCE(i.ContactEmail, 'null')
          FROM inserted i
          INNER JOIN Deleted d ON i.CustomerID = d.CustomerID
     END

END

We can see this handles the null appropriately.

In this case I’ve chosen to replace a NULL value with the word ‘null’. This means something to me, but I could have just as well replaced this with “blank” or any other word. In many applications a developer might display a null value as a blank, so choose what works for you.

I’m also including an example to show this works for multiple rows. Here I’ll update multiple rows and we can see each is inserted into my log.

This is a good reason to audit certain activities, as people will sometimes make these mistakes and updates lots of data.

This trigger is slightly more useful, and handles the NULL cases, but it still isn’t perfect. Imagine I need to parse out changes, or generate the reverse transactions, or even search for certain changes. Stuffing a lot of data into a single field is overloading it, and making it less useful over time. What we’d really want to do is separate pertinent data into different fields. In a NoSQL world, we might do this by using a JSON schema to track the before and after.

We could do that here, just stuff JSON into the log message and read it back out and de-serialize it.

SQL New Blogger

This post modified our trigger to address a previous design problem: not handling nulls. We also showed how to test the trigger with multiple rows. This shows I’ve added knowledge to my skillset and can test what I’m trying to do.

Write your own blogs that might examine what you’ve done poorly in the past and how to fix the problems you’ve identified, even in simple code. Many of us do this a lot.

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

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.

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.