Unanswered: Audit Trail
Is there a better way to notify user of changes to table? (I cannot buy 3rd party tool). I know this is a lot but it explains my thought process.
Currently I have the Main Table. A trigger on Main adds a record to Main_Audit.
A MainAudit (View) combines the 2. I select the top 2 for a particular ID by date and I have the Current and Last values.
I want to put this in a list and e-mail it (i have the e-mailer working).
New ColName1 = xxxx Old ColName1 = yyyy
New ColName2 = xxx1 Old ColName2 = yyy1
New ColName3 = xxx1 Old ColName3 = yyy1
Currently I am using this process.
1. Grab the current and last values from the view in a Cursor.
2. Fetch into hard coded variables i.e. @ColName1Old, ColName1New
3. Set up string using these variables.
4. Send the message.
This method means that I have a Stored procedure for Each of the Tables (#15) and I have to hard code the ColNames (upto 25 in each table) this = more work than I think is required.
I need a way to get the SQL results in an array (or another way) so I can access the individual column values dynamically. Is there a way to get the value of a col in a recordset. For example in VB I can use rst.Fields(3).value to get the value in the 3rd column.
My hope is to have 1 SP that I can Pass the Name of the View and the ID value and dynamically create the message for the old and new values for all the tables.
Someone out there has to be doing this.
Thanks in advance.
Hope is the feeling that the feeling that you have will not last very long.