August 11, 2010 at 11:48 am
We have some legacy systems that we replicate into SS2K on a nightly basis.
We have a requirement to report changes made to tables that are deemed to be “standing details”...eg Customer Table, Product Table etc. The legacy systems do not have any process of auditing change.
The changes are infrequent but for audit purposes we must be aware of them.
Whilst access to various parts of these systems are security driven...can we really trust those we delegate that trust to?
Because we do not know if anything has changed in such tables we truncate the SQL copy and reload the entire legacy table each night.
The task, therefore, is to compare existing SQL data (ie last night) against new SQL data (ie tonights), identify which rows have altered and to report the old and new values. Some of these tables are quite wide...in excess of 100 cols....and therefore the business requires only to see changed columns.
Take for instance table CUSTOMER...this has approx 80 columns and 30000 rows....during today a single change has been made to one customer ...a bank account sort code.
This is what is required in the report:
CustomerAc “1000”, SortCode old value “10-10-10” , SortCode new value “10-11-11” , Todays Date
No more no less...we already know cannot determine the exact time of change or the user...the legacy systems do not record it, but the fact that is has changed in the last 24hrs will suffice.
I would appreciate any feedback from others who have had to tackle a similar issue and to comment on my proposed method:
Prerequisites:
Have a replica of CUSTOMER table...called CUSTOMER_COPY...both have PK on the customer account number.
CUSTOMER_COPY has an update trigger on it that writes to an AUDIT table
AUDIT table
Action:
Truncate CUSTOMER_COPY
Copy CUSTOMER to CUSTOMER_COPY....trigger wont fire for insert.
Run import from legacy system to update CUSTOMER.
SELECT ACCOUNT, CHECKSUM(*) INTO #CUSTOMER FROM CUSTOMER --- ACCOUNT is the PK
SELECT ACCOUNT, CHECKSUM(*) INTO #CUSTOMER_COPY FROM CUSTOMER_COPY
Determine which ACCOUNT(s) has been altered by comparing the two temp tables.
UPDATE CUSTOMER_COPY from CUSTOMER for only the rows we know have been changed from above.
The UPDATE trigger on CUSTOMER_COPY will then populate AUDIT with the necessary detail for only the columns that have altered.
Question is.....does this seem like a feasible approach...bearing in mind this is SQL 2000 and third party tools are unavailable to me?...or, perhaps more hopefully, maybe someone has a better idea?
Kind regards Graham
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 12, 2010 at 10:59 am
apologies for the <bump>....any ideas any one?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 12, 2010 at 9:59 pm
hi, i am jones. thanks all
Mario Games[/url]
Free Mario Games[/url]
Ben 10 Games
Spiderman Games
Avatar Games
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply