Use audit value on record insert only

Question

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?

Answer 1

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
AS 
BEGIN
SET NOCOUNT ON;

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);
END

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

Update: As pointed out by Daniel, the update statement can be simplified by removing the WHERE and the JOIN to the inserted table.

UPDATE [SalesLT].[Customer]
	SET UserCreated = D.UserCreated
	FROM [SalesLT].[Customer] C
	JOIN DELETED D on D.CustomerID = C.CustomerID;

This will mean that the UserCreated field is always reverted, but it also makes the trigger that much simpler.

Comments

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.

---

What purpose does the WHERE clause serve here?

---

In database triggers the Deleted (Old values) and Inserted (New values) psuedo tables hold the table values before and after the update.  This means you can use them to make additional decisions about what else to do.   In this case we set the UserCreated value back to the Deleted/Old value whenever the Inserted or deleted values of UserCreated no longer matches.

---

Understood, but why is it necessary to check whether inserted/deleted matches? I tried a trigger like this without the whole WHERE clause and it seems to work fine - On update, it sets UserCreated to whatever was set by the audit value on record creation, what it was originally (i.e. the "deleted" value)
I guess without the WHERE, you would be performing an extra operation to overwrite with the same value, is that the only purpose?

---

Good observation.  I would say you are correct.  You can simply remove the WHERE and the JOIN to the inserted table.

---

Ok cool just wanted to be sure, thank you