471,092 Members | 1,539 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,092 software developers and data experts.

Audit trail..

A much lamented question, I guess..

I'm trying to create a simple audit trail.
log the changes to an SQL 2000 table, so that they are written into a
mirror table. The entire record, only the updated one, i.e. if say
only one field changes, the audit table will be inserted with one
record that has one field changed. if the record has been deleted, it
still will be written.
I'm not worrying about additional fields to the audit table containing
descriptive flags of what action took place yet. I just want the
mirror image for starters.

I got the script of the 'create table' off Query analyzer. created the
audit table.
the trigger looks like this:

CREATE TRIGGER dt_tbl1_audit
on tbl1
for insert, update, delete

insert into tbl1_audit
select * from inserted
the table has about 50 fields or so, so I tried to make do with *'s.
didn't work, so I tried copying and pasting the explicit list of field
instead (though I'm not sure why it needs that if the two tables are
identically structured).

in either case, if I update any field on the audited table, I get this
(after getting the warning that the results may take a long time to
process etc, the original table has over 100,000 rows)

"another user has modified the contents of this table or view,
the database row you are modifying no longer exists in the database
database error: insert error:
column name or number of supplied values does not match table

I'm not sure what's wrong, the two tables are identical (I copy pasted
the create table script with no changes). no other users except me on
this database.
i've removed all constraints and indexes from the audit table.
Jul 20 '05 #1
0 771

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Keith | last post: by
3 posts views Thread by Zlatko Matić | last post: by
6 posts views Thread by Parag | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.