Jump to content
  • 0
kgraham2121

Creating Audit Trail via Triggered Actions

Question

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

 

 

image.png.18910dee86a5275bb001738c4616e6e6.png

 

Share this post


Link to post
Share on other sites

3 answers to this question

Recommended Posts

  • 0

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

Share this post


Link to post
Share on other sites
  • 0

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

Share this post


Link to post
Share on other sites
  • 0

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)

image.png.55323b1147559371c9075efd2012866d.png

 

image.png.08dd83a0aec52e534900345e8db29595.pngimage.png.c7ba0a076329248fb1ee9e2f3f375e9b.png

 

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

image.png.21ea60ebb2e020c4befdaedf93bb297b.png

 

I hope this helps.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...