By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,925 Members | 1,832 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,925 IT Pros & Developers. It's quick & easy.

Audit trail..

P: n/a
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
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.