Morning Sugiarto, > I want to record all changes in tables (insert/update/delete). > > I can do this by creating triggers for each tables, triggers ex : > > ... > > Or, I can record changes via my app, which one do you thing is better, > regarding database performance and size?
Always, always always do whatever you have to do in the database and not in the application. I'm referring to data integrity, constraints, security etc here. For example, your application may decide that only certain values are allowed in a column and checks for it. This is fine. It may decide to do other validations and this too is fine, as the user gets to see errors and problems before they are sent to the database and a constraint violation error comes back. However, what happens if you decide that 4 values are now required, for example, yo have to amend the application code, test it and roll it out to all your users. With a constraint in the database, you have one script to amend and run, once only, job done. (ok, you still have to test it of course!) Equally, if your application is logging the changes then anyone with access to the database and the right tools, can connect and make changes which are not logged. Now, you have the problem on needing to write triggers for every column in every table that you need to log changes for. This can be done with a small bit of coding, but would it be easier to have one trigger per table and have an audit table, perhaps in a separate database if this is possible, where you simply write the old row to the audit table before updating the new row? Another consideration, do you want to store attempted changes as well? If someone changes a column then rolls back - are you still interested? You must put your validation and constrai9nts checking as close to the data, where it belongs, that means you do it in the database. You can still do checks and similar validation in the application - for ease of user use, but the main location has to be protecting the data. HTH Cheers, Norm. -- Norman Dunbar Dunbar IT Consultants Ltd Registered address: Thorpe House 61 Richardshaw Lane Pudsey West Yorkshire United Kingdom LS28 7EL Company Number: 05132767
