×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

How do I log new records to another SQL table
2

How do I log new records to another SQL table

How do I log new records to another SQL table

(OP)
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.

RE: How do I log new records to another SQL table

(OP)
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?

RE: How do I log new records to another SQL table

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

RE: How do I log new records to another SQL table

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

RE: How do I log new records to another SQL table

(OP)
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

RE: How do I log new records to another SQL table

You can take a look at 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)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources