I would expect an audit table to look like this:
id - guid
actionTakenOnObjectId - guid, possibly fk to object table
typeOfObjectActionTakenOn - possibly have this if you are sharing the audit table with multiple other tables.
actionDate - UTCDateTime
actionData - string data associated with the change. ie first name from x to y
userId - guid, Id of the user that took the action
The key thing with the audit table is that you are always able to add entries. With this design the GUID PK means you will never get a duplicate record and can insert records from multiple computers and threads as you like.
Also, you can record things like : user x viewed record y, where no data change takes place.
In your situation you have a couple of odd things.
- the extra int Id col. I'm not sure what your reasons are for this and you say it can change. Lets leave it out of the equation for now
- You are making an exact duplicate of the existing row rather than just storing a subset of info about the event.
Normally I would say that a datetime column is doesn't have enough resolution to be a primary key. You can have two events happening at the same time and the second will fail to insert due to having a duplicate key.
However, in your case because your audit is directly related to a change of the database row, it would be possible to encapsulate the update and audit insert in the same transaction. Adding a tick onto the LastUpdatedDate where you detect that it hasn't changed since the last update.
Assuming you use the unchanging GUID rather than the changeable int id it would then be possible to ensure that the time is always unique and you can always insert an audit record.
If you do take this approach though, you might be better served by instead adding a LastVersion - guid column which explicitly references the previous audit record for the row.
Overall though this is complex approach, akin to Event Sourcing. and relies on enforcing the order of object change events. If you get two changes to the same version of the object. say I update the name, while another user updates the address. Then you may want to reject the second change with a "Another user has changed the object while you were editing" style message.
Obviously every case is different, but, I think in general I would advise for using a new PK on an audit table and against having an audit table which I suppose is a kind of transaction log for another table.