February 22, 2011 at 10:02 am
Please could someone help with the Immediate decision..
we have an application and there are 500/600 Users who access the application.
There are around 30 to 40 main tables exists for the application.
some tables have 5 million,some have 10 million and some tables have more then 40 million records....
my manager would like to know if any change happened in any of the row/column in any of the table...if yes then she wants to have the old record saved some where.....
for example: we have a Customer table...which has 40 columns in the table....
if any one of the column/row value has been changed by any user then i have to keep track of the old record...
Below are my Ideas...
I can write trigger on 30 to 40 tables...
but below are the problems...
It makes the application slow...and some records are updated by import process from the web services ...so they cannot
handle by the trigger....
i thought of mainting the history tables were i will be loading the records which been updated daily...but some of the tables do not have the modified date...so i do not know what record has been updated in that table....and if i keep on inserting the records into the history tables...then the table might keep on increasing the volume...
In the meeting team had couple ideas...they want to remove the update button on all the modules on the application...
and to remove the web services option for the users ...which will not have the update option ...If the user wants to update anything on the application..they will not be able tooo...they can only add new records...
But they did asked me instead of removing the update option on all the modules on the application...Is there any other way
that i can do from database side...that i can keep track of any column/row changes in any of the table in that database...
Could someone please help me with some suggestions...i have to update the team as soon as possible...
February 22, 2011 at 10:49 am
A few questions:
1. Do the applications which access the DB use stored procedures or dynamic SQL?
1a. How many stored procedures does the application utlize?
2. Do you have the a duplicate of the production DB on another server with which you can test any revisions that you may make?
If the applications do use a few stored procedures, have you examined the the possibility of altering these few procedures into a single stored procedure which uses a MERGE statement.
Here is a good starting point to read up on the merge statement:
February 22, 2011 at 11:01 am
Application uses both..some places it has dynamic SQL and some places we are using stored Procedures..
Yes i do have the copy of the Production database on the test environment..
If i use Merge on the Stored Procedure then some tables have like 40 to 50 million records...
won't it take long time....to check 40 to 50 million records with another table...
February 22, 2011 at 11:01 am
I wrote some articles on audit logs a while back. They might help:
http://www.sqlservercentral.com/articles/Auditing/63247/
http://www.sqlservercentral.com/articles/Auditing/63248/
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 22, 2011 at 12:27 pm
wouldn't the new to SQL2008 Change Data Capture do what you need? plenty of articles on that, when i tested it it worked really nice.
Lowell
February 22, 2011 at 12:37 pm
Read the link I sent to you, there are methods of shortening the time required for a merge statement. It will all depend on the where clause utilized.
Also be sure to read what GSquared links will lead you to .. it is also an effective method
Also follow up on Lowell's links
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply