Creating Audit Trail via Triggered Actions

[kgraham2121]

Hi,

I'm trying to to created a triggered action that populates and Audit Log Summary Table (1 record per event - ) and an Audit Log Details Table ( 1 record per changed Field/Column).

Workflow is upon Insert or Update to Orders Table want to Log Changes.  Trying to keep Log tables as simple as possible. Assume following Table Structures:

Orders Table:

• Accession ID (Primary Key)
• Editable fields 1
• Editable fields 2
• Editable fields 3
• Editable fields 4
• Editable fields 5
• Last Modified  - Timestamp
• Last Modified by - User Stamp

Audit Log Summary Table:

• Summary ID (Primary Key)
• Accession ID(Foreign Key from Orders)
• Modified by User
• Modified Date
• Change Type (I, U, D)

Audit Log Details Table:

• Audit Log Detail ID (Primary Key)
• Summary ID (Foreign Key from Summary Table)
• Column Name( Field that changed in orders table)
• Old Value
• New Value

I know how to create the trigger to insert Accession ID, Modified by User, and Modified Date into Summary table upon Insert, Update, or Delete.  I'm not certain how to stamp the change type field - based on Insert, Update or Delete.

What I'm really struggling with is next step.  For each event, how do I identify Fields that changed, and insert changes into details table?  An would I be nesting this into original trigger in Orders table, or creating a new trigger in Summary Table?

 

Pasting 1st part of trigger for reference.  Any help or guidance greatly appreciated...

 

 

 

[Glitch]

If you want to retrieve the old value and the new value at the same time, like what I mentioned on the previous response, all you need to do is to join the inserted table with the own table. 

 

Here's a screenshot on how to do that. (This is just and idea how to do this, though I don't know your exact workflow)

 

 

On the table where you're inserting, this will be the value:

 

I hope this helps.

[Glitch]

Hi there,

 

For starters, I think you should have separate triggers for Insert, Update, Delete. 

Currently, I don't think it's possible to determine what Table Action fires the trigger. The easiest way I can think of, is to create separate triggers, this trigger will populate if it's inserted, updated, or deleted. 

 

With regards to your next question as to "how do I identify Fields that changed, and insert changes into details table", 

If you'll be pertaining to the "UPDATE" Action on trigger, what you can do is to Join the #inserted table with the actual (Self table) so you can get the previous value, and the inserted value. 

 

I hope this helps.

 

Glitch

[kgraham2121]

Thank you @Glitch,

1 trigger per action really simplifies this - thanks for that direction.

I'm on to the 2nd step - identifying changes and inserting into Details Table.  I understand joinging #inserted with Source(Orders Table).  I feel like I may need a 2nd join and I'm not certain where to do it.  Let me explain:

Desired output to Details Table includes:

• 1 record for each change for each Summary ID(Foreign Key)...i.e. user updates 3 fields in source table.  Part I of trigger inserts into Summary Table - Stamping with Summary ID, plus Time and User Stamps.  We want Part II of trigger to create 3 new records in Details Table - All with same foreign key(Summary ID - this is what I think I need the additional join for - to bring this ID over) - 1 record per field change.  I can't figure out how to insert Column(Field) name that changed.  Once I insert that - I know how to bring in Old and New value using the Join of #inserted and Source Table)

Hope this makes sense...Appreciate your time, attention, and assistance...