Use audit value on record insert only

0 votes

I have a "UserCreated" field with an audit value to capture the username that created the record. However if a different user edits an existing record, the field gets overwritten by the editing username.

I see the Audit Values help page has directions on Capturing [Created] and [Updated]. Would this method be the way to go or is there something simpler? (I don't really need to capture the Updated user.)
How would the insert trigger look?

in How To by (1.2k points)

1 Answer

0 votes
Best answer

You can hack the process by having a database update trigger revert any value at update.

This would mean that you could setup a UserCreated field as an audit value. The initial insert would be populated with the username. Any updates to that column would be reversed by the trigger.

An update trigger to ignore the field change would be...

CREATE TRIGGER  [SalesLT].[trig_Customer_Update]
   ON [SalesLT].[Customer] AFTER UPDATE

UPDATE [SalesLT].[Customer]
	SET UserCreated = D.UserCreated
	FROM [SalesLT].[Customer] C
	JOIN INSERTED I on I.CustomerID = C.CustomerID
	JOIN DELETED D on D.CustomerID = C.CustomerID
	WHERE I.UserCreated <> D.UserCreated 
		OR (I.UserCreated is null and D.UserCreated IS not null)
		OR (I.UserCreated IS not null and D.UserCreated IS null);

Let me know you know if you find an easier way to ignore/revert field updates in a trigger.

by (52.4k points)
selected by
Thanks Anthony this worked. I would suggest a feature request of adding the ability to choose when an Audit Value fires (e.g.: Insert; Update; Insert & Update) to more easily set up separate [Created] and [Updated] audit fields.
@Daniel, That sounds like an interesting idea.  I will keep that in mind.
Welcome to the dbFront Q&A site, where you can ask questions and receive answers from other members of the community.
 | Minimalist Answer Theme by Digitizor Media
Powered by Question2Answer