I have created a trigger that will audit all changes to the database. I have copied this trigger to all tables in my database so that every single amendment is logged.
My front end application makes amendments to the databse and sometimes, it makes multiple changes to different tables in the same transaction. When this happens, I would ideally like to reflect this in the audit table. What I mean by that is have someway of being able to tell which changes were part of the same transaction. For example, clicking Complete in the front end app writes a line to the sales table and decreases the stock in the stock table (just one of many possible examples). This would make 2 additions to the audit table and it would be great if there was a way in which I could tell from the audit table that those two changes happened as part of the same action, without having to make any custom development from the front end. For example, if they both had the same timestamp to the millionth of a second, I could convert the time and date to a number and that would be the unique identifier. The reality however is that I know that each trigger is activated independently.
Is there a way of achieveing what I want? Any suggestions or similar concepts will help set me off in the right direction many thanks for looking.