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 `NULL`s. 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 `NULL`able data type, add a value for all `NULL` rows, and set the column to `NOT NULL`. If we do want to update a `NULL`able 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 of`last_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.
