Should I remove NOT NULL for columns changed by AFTER INSERT trigger?

Question

(SQL Server 2014 Express)

Hi,

I reference this article: http://msdn.microsoft.com/en-gb/magazine/cc164047.aspx

I have this AFTER INSERT, UPDATE, DELETE trigger to set or update DateCreated, DateModified, and WhoUpdatedID columns in several tables (delete processing to be added later):

CREATE TRIGGER [dbo].[TR_dim_TypeOfClaim_Audit]
    ON [dbo].[dim_TypeOfClaim]
    AFTER INSERT, UPDATE, DELETE
AS
    SET NOCOUNT ON
    DECLARE @event_type [char]

    --Get Event Type
    IF EXISTS(SELECT * FROM inserted)
    IF EXISTS(SELECT * FROM deleted)
        SELECT @event_type = 'U'
    ELSE
        SELECT @event_type = 'I'
    ELSE
    IF EXISTS(SELECT * FROM deleted)
        SELECT @event_type = 'D'
    ELSE
    --no rows affected - cannot determine event
        SELECT @event_type = 'K'

    IF @event_type = 'I' BEGIN
        --Date Created
        UPDATE t
            SET DateCreated = GETDATE()
            FROM INSERTED e
            JOIN [dbo].[dim_TypeOfClaim] t ON e.[TypeOfClaimID] = t.[TypeOfClaimID]
      ;
      SELECT @event_type = 'U' --also do UPDATE processing
    END

    IF @event_type = 'U' BEGIN
        --Date Modified
        UPDATE t
            SET DateModified = GETDATE()
            FROM INSERTED e
            JOIN [dbo].[dim_TypeOfClaim] t ON e.[TypeOfClaimID] = t.[TypeOfClaimID]

        --WhoModifiedID
        UPDATE t
            SET WhoModifiedID = u.UserID
            FROM INSERTED e
            JOIN [dbo].[dim_TypeOfClaim] t ON e.[TypeOfClaimID] = t.[TypeOfClaimID]
            JOIN [dbo].[dim_Users] u ON u.[Username] = dbo.udfUserName()
    END

    IF @event_type = 'D' BEGIN
        no_op:  --Nothing for now
    END
GO

The integrity rules for DateCreated, DateModified, and WhoUpdatedID is NOT NULL. Which is technically correct, but are never input by the end user. This causes errors when new records are added.

Should I just change these columns to NULL allowed, or to change the trigger to INSTEAD of? I'm not sure the best practice here.

If it matters, I intend to implement full auditing of all data changes later, either via triggers or change tracking (I need to read up on change tracking to see if it meets my needs).

Thanks for the help...

Update:

@Bogdan: Sorry, my comment wasn't clear. Based on everyone's feedback so far, this is what I currently have:

1) I've left DateCreated, DateModified, and WhoModifiedID as NOT NULL

2) I've created default values as getdate(), getdate(), and 0 respectively. All values are just to get past the NOT NULL constraints for a new record. I suppose one could argue that, with the trigger, the NOT NULL constraint is redundant - the trigger would ensure the values are NOT NULL anyway. I'm a bit unclear in this regard what is best practice. But I don't want these columns to ever be NULL, and the constraint makes this obvious.

3) My current trigger is now:

ALTER TRIGGER [dbo].[TR_dim_InjuryType_Audit]
    ON [dbo].[dim_InjuryType]
    AFTER INSERT, UPDATE
AS
    SET NOCOUNT ON

    DECLARE @CurrentUserID INT;
    SELECT  @CurrentUserID = u.UserID 
    FROM    [dbo].[dim_Users] U
    WHERE   u.[Username] = dbo.udfUserName()

    UPDATE  T
    SET     DateModified = GETDATE(),
          WhoModifiedID = @CurrentUserID
  FROM    INSERTED E
    JOIN    [dbo].[dim_InjuryType] T ON e.[InjuryTypeID] = t.[InjuryTypeID]

4) BTW, dbo.udfUserName() is merely:

-- =============================================
-- Author:      Scott Bass
-- Create date: 04JUL2014
-- Description: Return userid without the domain
-- =============================================
ALTER FUNCTION [dbo].[udfUserName]
(
)
RETURNS varchar(20)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @UserName varchar(20)

    -- Add the T-SQL statements to compute the return value here
    SELECT @UserName = substring(suser_sname(),charindex('\',suser_sname())+1,99)

    -- Return the result of the function
    RETURN @UserName
END  

5) Thanks for the hint re: not using EDIT TOP 200 ROWS in SSMS

This is working as I desire. Further comments on improvements/best practice more than welcome, plus will help those that find this thread later.

I'm getting an annoyance in my Access front end that is perhaps related to the triggers but really is a separate subject for a separate post. However, I include this link for completeness, in the chance someone is interested: http://www.utteraccess.com/forum/User-Edited-Record-Sav-t2019558.html

Answer 1

I would use / I've used following solutions (1) and 2.1)):

1) DateCreated is mandatory: NOT NULL plus a default constraint using GETDATE()

ALTER TABLE [dbo].[dim_TypeOfClaim]
ADD CONSTRAINT DF_dim_TypeOfClaim_DateCreated DEFAULT (GETDATE()) FOR DateCreated

Setting DateCreated using GETDATE() as default value is a better solution (in terms of performance) than using an AFTER INSERT trigger with an UPDATE ... SET DateCreated = GETDATE() ....

2.1) DateModified and WhoModifiedID should be NULLable and if you are using [only] stored procedures to UPDATE the rows from [dbo].[dim_TypeOfClaim] then I would change these procedures to update, also, the DateModified and WhoModifiedID columns. This way, you could delete this AFTER UPDATE trigger. The downside of this approach is that if someone run an ad-hoc script that UPDATE dim_TypeOfClaim rows could easily forget to update ,also , DateModified and WhoModifiedID columns.

or

2.2) DateModified and WhoModifiedID should be NULLable plus an AFTER UPDATE trigger:

-- tr = trigger, U = INSERT only trigger
ALTER TRIGGER [dbo].[trU_dim_TypeOfClaim_Audit] 
    ON [dbo].[dim_TypeOfClaim]
    -- This trigger should be activated only by UPDATE statements (or MERGE ... UPDATE SET ...)
    -- This trigger shouldn't be activated by INSERT or DELETE statements
    AFTER UPDATE 
AS
BEGIN
    DECLARE @CurrentUserID INT;
    SELECT @CurrentUserID = u.UserID 
    FROM   [dbo].[dim_Users] u 
    WHERE  u.[Username] = @CurrentUserName;

    UPDATE  t
    SET     DateModified = GETDATE(),
            WhoModifiedID = @CurrentUserID
    FROM    INSERTED e
    JOIN    [dbo].[dim_TypeOfClaim] t ON e.[TypeOfClaimID] = t.[TypeOfClaimID]

END

Answer 2

It doesn't make sense (to me) to have DateModifed and WhoUpdatedID as NOT NULL, since they will always be NULL when a record is inserted.

In my opinion, only DateCreated should be NOT NULL, and it should be set by the database, either by a trigger or Stored Proc.