audit trail...

[Me]

Hi...
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

AS
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
names
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
error:
(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
definition"

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.
thanks

[John Bell]

Hi

It seems that you are having problems with posting!!!

If speed is important you should think about keeping simple copies of the
inserted and deleted tables (possibly with other columns such as a timestamp
and user name) as this will mean that the trigger the least processing and
not causing you transactions to be open for a elongated period. The work of
resolving what columns have been updated can then be done either when
reporting or at a more convenient time.

If you wish to do this processing withing the trigger check out Books online
under the "Create Trigger" topic, you can find information about using the
UPDATED() and COLUMNS_UPDATED() functions and example of how to use them.
Another alternative method is to use a log file reading product such as
those from Lumigent (Lumigent Log Explorer) http://www.lumigent.com/ or PI,
http://www.logpi.com
and process the information in the log files.

HTH

John

"Me" <he****@lycos.com> wrote in message
news:2d**************************@posting.google.c om...

Hi...
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

AS
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
names
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
error:
(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
definition"

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.
thanks

[Erland Sommarskog]

Me (he****@lycos.com) writes:

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

AS
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
names
instead (though I'm not sure why it needs that if the two tables are
identically structured).
Depends on what columns there are in the tables. If you have an IDENTITY
colunm in the source table, the corresponding table in the audit table
cannot have the IDENTITY property. And if there are timestamp columns,
you would have to make them binary(8) in the target table.

In any case, some sort of a primary key for the target table would be a good
idea.
in either case, if I update any field on the audited table, I get this
error:
(after getting the warning that the results may take a long time to
process etc, the original table has over 100,000 rows)
So where does this warning come from?
"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
definition"

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.

Well, we don't even know the definition of the tables, so how could we
tell what is going on?

Do you get this error when you perform an update from Query Analyzer? If
so, can you cut and paste the complete error message? The error message
should include procedure name and line number where the message appears.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Me]

yes, I did have trouble posting, I do apologize..

IE6 reported some sort of 404 error when I clicked submit in the
dejanews post. I assumed it didn't & tried several dozen times until I
accidentally discovered that it did post (dejanews says it takes
several hours to post, so traditionally I wouldn't have discovered
this, but this time I stumbled on another site, sort of a gateway to
google groups, which showed my post immediately... seems useful.
http://news-reader.org/comp.databases.ms-sqlserver/
re the audit, I did get the code to work both ways, with *'s notation
and also with detail listing of all the fields. so this post is
generally for the benefit of other befuddled customers on my trail..

The following trigger works. audits updates/inserts on the table
tblSource, into the log table tblSource_Audit which has the same
structure plus the two fields 'LogActionType' (varchar 10) and
'LogDate':

CREATE TRIGGER dt_insupd
on tblSource
for Insert,Update AS
INSERT INTO tblSource_audit
select 'insert/upd',GetDate(),
* from Inserted ins
GO
Still, I thought it would be safer (future maintenance wise, so the
trigger won't break if fields are added to the source table)
to convert the whole thing into detailed column notation (too long to
list here), and Later add a little condition code to it that would
post 'insert' and 'update' strings identifying the two operations and
not the combined string above:
CREATE TRIGGER dt_insupd
on tblSource
for Insert,Update AS

Declare @ActionType VARCHAR(10)
Declare @DeleteCnt int
set @DeleteCnt = (select count(*) from deleted)
if @DeleteCnt = 0
begin
set @ActionType = 'Insert'
end
ELSE
set @ActionType = 'Update'

INSERT INTO tblSource_audit
select @ActionType,GetDate(),
* from Inserted ins
GO
Thanks everyone for your help. Any more comments, of course, welcome.