Default Values, Triggers, and You

A friend of mine sent me an email the other day asking about default values in SQL Server. I realized that I’ve had to think about this a few times over the years and I’ve been asked about it more than once, too.

Setup

We need a table first, right? We’ll also want a few sample rows in there.

CREATE TABLE Employees (
  emp_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  emp_name varchar(50) NULL);
GO

INSERT INTO Employees (emp_name) VALUES ('a');
INSERT INTO Employees (emp_name) VALUES ('b');
GO

SELECT * FROM Employees;

/*
emp_id      emp_name
----------- --------------------------------------------------
1           a
2           b
*/

How Defaults Work

So far we just have two rows in our two column table. It’s pretty boring. Let’s add a default value:

ALTER TABLE Employees
ADD last_modified DATETIME NULL CONSTRAINT Employees_last_modified 
DEFAULT CURRENT_TIMESTAMP;
GO

We might as well add some new rows while we’re having fun with our employees, right?

INSERT INTO Employees (emp_name) VALUES ('c');
INSERT INTO Employees (emp_name) VALUES ('d');

What’s it look like now?

SELECT * FROM Employees;

/*
emp_id      emp_name                                           last_modified
----------- -------------------------------------------------- -----------------------
1           a                                                  NULL
2           b                                                  NULL
3           c                                                  2010-12-06 18:21:37.787
4           d                                                  2010-12-06 18:21:37.787
*/

Hold up. Employees 1 and 2 don’t have a last_modified value. Why not? Well, that’s because we’ve told SQL Server that our last_modified column can allow NULLs. They’re allowable in our table. If we wanted to automatically provide a default value when we added the constraint, we could do so by specifying the datatype as DATETIME NOT NULL. A best practice would be to add the column as a NULLable data type, add a value for all NULL rows, and set the column to NOT NULL.

If we do want to update a NULLable column and set it to the default value, we just issue an update using the DEFAULT keyword for the value. If that makes no sense, perhaps this example will help:

UPDATE Employees
SET last_modified = DEFAULT
WHERE last_modified IS NULL ;

What About Updates?

UPDATE Employees
SET emp_name = 'zzz'
WHERE emp_id = 3;

SELECT * 
FROM Employees
WHERE emp_id = 3;

/*
emp_id      emp_name                                           last_modified
----------- -------------------------------------------------- -----------------------
3           zzz                                                2010-12-06 18:21:37.787
*/

As you can see, when we update employee 3, it doesn’t change the value oflast_modified. That’s because the default value is only set on insert. We could specify DEFAULT in our UPDATE statement, but then we’d need to specify that every time we update the table. What can we do?

The Answer is Triggers

That’s right: triggers. If we want to track the modification timestamp of an object in the database, we need to use a trigger to keep things updated:

CREATE TRIGGER TR_Employees$AfterUpdate ON dbo.Employees
AFTER UPDATE
AS
BEGIN
  UPDATE  e 
  SET     e.last_modified = CURRENT_TIMESTAMP
  FROM    dbo.Employees e 
          JOIN inserted i ON e.emp_id = i.emp_id;  
END
GO

UPDATE Employees 
SET emp_name = 'asdf'
WHERE emp_id = 1;

SELECT * FROM Employees;

/*
emp_id      emp_name                                           last_modified
----------- -------------------------------------------------- -----------------------
1           asdf                                               2010-12-06 18:34:04.340
2           b                                                  NULL
3           zzz                                                2010-12-06 18:21:37.787
4           d                                                  2010-12-06 18:21:37.787
*/

And that, my friends, is how we keep a modification timestamp up to date.

Menu