November 20, 2006 at 8:15 am
Hi,
I was wondering if anybody knows how to track changes in another database running on the same box. I know one way is to use trigger, but we don't have a room to use triggers.
Anything else anybody can think of?
Thank you,
Ignas Chilewa
Dublin, OH
November 20, 2006 at 8:23 am
What do you need to keep track of exactly?
Also why can't you use triggers for this?
November 20, 2006 at 8:31 am
Ninja,
I want to keep track of changes on one of n columns in this external table which has more than n number of columns.
Trigger has been ruled out as this database is not owned by us.
Ignas
Dublin, OH
November 20, 2006 at 8:40 am
Track changes of the data or data type/constraints...?
Do you even have access to that table. Can they grant you access to a view of the table? Then you can save a local copy and hten regulaly check for differences.
November 20, 2006 at 8:46 am
Yes Ninja,
Track changes of the data and not data types or otherwise..
Yes, I do have read access to this table and we would like to minimize duplication as much as possible.
Ignas
Dublin, OH
November 20, 2006 at 8:52 am
You can't limit duplication here because the way to do this is to use a trigger or code it directly in the application. So just keep a local copy of the table and periodically check for changes... then audit the changes any way you guys see fit for the business requirements.
November 20, 2006 at 8:56 am
Thanks Ninja for the great inputs.
I will sit down with my team members to try to digest these two possibilities, trigger and using a local copy.
Again thank you.
Ignas Chilewa
Dublin, OH
November 20, 2006 at 8:59 am
Maybe I can fill in the gaps... What exactly is the need that you need to fulfill with data auditing?
November 21, 2006 at 12:27 pm
Does the table have a column to track update times? If so, you can simply query the table for anything with an update time newer than the last time you checked.
Personally, on data I want to track, I add two datetime columns. One column simply tells me the datetime that the record was initially inserted and has a default of getdate(). The second column tells me the last datetime it was updated and has a default of getdate() plus a trigger that updates it any time the record is updated in any way.
Obviously, in this case, if the table you want to track does not already have this, you don't have the luxury of adding it yourself. If it does not, I would suggest keeping a table in your database that only tracks the primary key column(s) and the CheckSum value of the whole row (and maybe the columns I mentioned before). If you look up Binary_Checksum, CheckSum, and Checksum_Agg in Books Online, there will be examples of how to do this.
November 21, 2006 at 2:08 pm
Robert,
Thank you for your great idea, I like the idea of using checksum.
How expensive it is in terms of performance?
Ignas
November 21, 2006 at 2:15 pm
It is very good performance wise. Much better than returning the entire row.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply