November 7, 2017 at 2:44 am
I can't get my head around this!
I want to audit changes to a table. Multiple (10 +) fields may be changed at one time. Inc. 1 x geometry type. I have a trigger already & an audit table that works BUT I'm struggling with the updates. I'm capturing all the inserted / deleted values in my audit table fine.
How can I log WHICH field/s have been changed?
I need to be able to compare all fields & also somehow compare this geometry field.
I could capture the actual SQL that's being run, it just seems a bit amateur.
Don't need all the code spelling out, just some pointers.
SQL 2008 R2.
Yours in eternal gratitude!
November 7, 2017 at 2:55 am
snomadj - Tuesday, November 7, 2017 2:44 AMHow can I log WHICH field/s have been changed?
It would be easier for us to help if you posted the code for your trigger. But you just need to compare the values in the Inserted virtual table to those in the Deleted to see which columns have changed.
John
November 7, 2017 at 3:41 am
Understood and thanks for replying.
I guess I have two questions. I'm trying to make it simpler because I waffle!
1 - how do I compare two rows of data & pull out all changes.
my audit table = CREATE TABLE auditTable (a int PRIMARY KEY, b INT, c INT, sqlOperation NCHAR(2), udpdateDate DATETIME)
I have the following 2 records:1,2,3,'U1','7/11/17 9am'
1,4,5,'U2','7/11/17 10am'
How do I compare the above 2 rows, pull out WHICH fields have changed (b&c) and the before and after vals.
My trigger records a sqlOperation value of 'U1' from deleted, 'U2' from inserted when it's an update.
2 - After that, I need to decide how to deal with the geometry field because in fact the table is quite a bit larger & has this 1 geometry field that I need to use .steEquals on.
November 7, 2017 at 3:54 am
snomadj - Tuesday, November 7, 2017 3:41 AMUnderstood and thanks for replying.I guess I have two questions. I'm trying to make it simpler because I waffle!
1 - how do I compare two rows of data & pull out all changes.
my audit table =
CREATE TABLE auditTable (a int PRIMARY KEY, b INT, c INT, sqlOperation NCHAR(2), udpdateDate DATETIME)
I have the following 2 records:1,2,3,'U1','7/11/17 9am'
1,4,5,'U2','7/11/17 10am'
How do I compare the above 2 rows, pull out WHICH fields have changed (b&c) and the before and after vals.My trigger records a sqlOperation value of 'U1' from deleted, 'U2' from inserted when it's an update.
Why do you want to compare the rows once they're in the audit table? Are you writing a report on what's changed? What are your expected results?
2 - After that, I need to decide how to deal with the geometry field because in fact the table is quite a bit larger & has this 1 geometry field that I need to use .steEquals on.
Not sure what your question is here? I've never used geometry types, but isn't it as simple as using .steEquals instead of "="?
John
November 7, 2017 at 11:42 am
Use the COLUMNS_UPDATED() Function in your trigger and log that as well if you like.
Alternately a more elegant and simpler solution would be to duplicate SQL Server 2016's temporal table schema requirements of begin and end time stamps and all the same columns for the table and history table and an instead of trigger to enter the old rows into the history table before applying the action to the table.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply