(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
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