How do I log new records to another SQL table 2

[noddy]

I need to manage changes to several SQL tables, they can all go into a single or many log files.
Is there a way to do this within the SQL environment?
We are using an accounting system and the new reporting requirements are to report any changes to key tables. There is nothing in the software to allow for this and we are prevented from using VB to modify the application so need a passive method that we can then use Crystal Reports to pick up on the changed records within a period.

 

[noddy]

I have been told that the Manage Triggers is a good place to start, use that to add records to new tables and include whatever fields are needed plus the date/time and user id.
Anyone with experience of this?

 

[johnwm]

You will probably get a more detailed answer to this depth of SQL question here:

http://www.tek-tips.com/threadminder.cfm?pid=183

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

 

[rtmpxr]

Yes, the triggers represent a good choice. For example, write a trigger for the "Inventory" table in the "after insert" event. The SQL for your "LogEvents" table will be:
insert into LOGEVENTS (day, user, table) values (Now, current_user, 'INVENTORY')
Here "Now" and "current_user" are system variables of the SQL engine, so much of your work is already done. This example is taken for Firebird, but you'll find easy the corespondence with your database.

HTH

 

[noddy]

HTH,

Many thanks for that. My son has now done all the work and we have installed it on the clients site. Very neat. The only problem is to find the user ID, the way the application is used, the user is always "scala" (to do with security issues), the application has its own user ID security but that is not linked to SQL, we are looking at capturing the machine name instead, that is better than nothing.

Anyone who would like to see the full script please let me know and I will send it. Particularly useful for those involved in the Sarbanes Oxley Act, audit trails etc. Now looking at logging changes to discounts given on sales orders and changes to quantity for purchase orders.

Colin

 

[rtmpxr]

You can take a look at

http://www.firebirdsql.org

. Firebird is a full featured SQL engine and believe or not, is also free. I used it in all my commercial products an I have no complaints. All security problems you talk about, will be very easy to implement.

Hope That Help (HTH)