Hi, I am new in the team . I have just finished designing accounting system. The customer is looking forward to have a shadow database that traces all the transactions, modifications of vouchers done by users. Is there white papers or articles that can direct me for best performance.
Let me explain what I need exactly. I have Sql accounting database. In the database I want to trace the insertion and amendment of the records in tables. Let's take a journal voucher as an example. The voucher contains two tables the main JV table (Master) and the details JV table (slave). When a user inserts a new record in JV another database should record the user Name, Time and the record inserted. Once another user amends the same record later a copy of the new look of the record should be inserted into the (shadow database) , the user and the time the action took Place. Eventually, I will be having a database the is running the accounting system and another database (I called it shadow database) that registers all the transactions on the vouchers. Also I want to be able to trace the behavior of any particular user on the database. Many Thanks again
This is pretty basic data warehouse theory, and depending on the size of your accounting system I would probably recomend using a third party ETL-tool (extract, transform, load) like Informatica PowerMart or Business Objects Data Integrator. But if the system isn't too big triggers could also do the trick but it could get messy after a while. In my opinion triggers should be avoided unless you use them for keeping a "last-modified"-column up to date or keeping track of DDL-operations (DDL-triggers).