SQL Server alternatives to triggers for default-value logic

Question

In SQL Server, we have a pattern of creating triggers to set default date and GUID on insert:

CREATE TRIGGER [dbo].[trgInsertMyTable] ON [dbo].[MyTable] FOR INSERT AS
BEGIN
 UPDATE [dbo].[MyTable] SET Guid = NEWID()
 FROM [dbo].[MyTable] T
 INNER JOIN [Inserted] I On T.IdMyTable = I.IdMyTable
 WHERE NULLIF(I.Guid, '00000000-0000-0000-0000-000000000000') IS NULL;
 UPDATE [dbo].[MyTable] SET [TimeStamp] = GETDATE()
 FROM [dbo].[MyTable] T 
 INNER JOIN [Inserted] I On T.IdMyTable = I.IdMyTable
 WHERE NULLIF(I.Timestamp, '0001-01-01 00:00:00.0000000') IS NULL;
END

It's checking whether the inserted values are null or essentially zero value. It feels like there's a better way to accomplish this.

Simply using a default value function for the field would allow the zero value to populate the field, which we don't want.

Do cleaner options exist?

Would it be a viable option to update the inserted values and replace them with null?

Even better, in most or all of our cases we should be able to disallow that value to be set and instead always use a default value. Do built-in options exist for this path in SQL Server?

Answer 1

You can easily prevent those 0 values from getting inserted by using a check constraint, as exampled below. This will require any client who wants to write or update those values to either specify a valid value or to leave it blank and let the database use the defined defaults.

CREATE TABLE dbo.Test 
 (
 TestID INT NOT NULL IDENTITY (1,1)
 , TestDate DATETIME2(7) NOT NULL CONSTRAINT DF_Test_TestDate DEFAULT (GETDATE())
 , TestGUID UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_Test_TestGUID DEFAULT (NEWID())
 , CONSTRAINT CHK_Test_TestDate CHECK (NULLIF(TestDate, CONVERT(DATETIME2(7), '0001-01-01 00:00:00.0000000')) IS NOT NULL)
 , CONSTRAINT CHK_Test_TestGUID CHECK (NULLIF(TestGUID, CONVERT(UNIQUEIDENTIFIER, '00000000-0000-0000-0000-000000000000')) IS NOT NULL)
 )

However, if you have a simplistic ETL that's just dumping the values into the table and the source data HAS these 0 values then your only option is to use the triggers as you have or change the import process to be smarter.

The use of constraints above will prevent them from writing the 0 values, but it will mean that any attempt to write a 0 value will fail.

• Charlieface: Why not just CHECK (TestDate <> '0001年01月01日 00:00:00.0000000') given you already have a NOT NULL constraint