How to find out which field in a table is updated

[lphuong]

When someone modifies a field in a table, I like to find out which
field he/she changed.
Currently, only the LAST person to modify an object is saved. For
example, if I modify an Application record, I will see "MyName" in the
"tblApplications.UpdatedBy" field and the date and time I updated it.
But it doesn't keep an historical record. We would like these changes
to be stored in a file or a table or something.
Please help

[Allan Mitchell]

Then implement triggers to log to an audit table the changes to a data table

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"lphuong" <lp*****@neh.gov> wrote in message
news:b6**************************@posting.google.c om...

When someone modifies a field in a table, I like to find out which
field he/she changed.
Currently, only the LAST person to modify an object is saved. For
example, if I modify an Application record, I will see "MyName" in the
"tblApplications.UpdatedBy" field and the date and time I updated it.
But it doesn't keep an historical record. We would like these changes
to be stored in a file or a table or something.
Please help

[L Phuong]

Allan, would you please show me how to do it in SQL Enterprise or in
VB6. I'm a novice in this subject.
Thank you.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Allan Mitchell]

OK

--So say you have a table

CREATE TABLE MyRealTable(ColPK int identity(1,1) Primary Key, col1 int)

--You now emulate that table with an audit version

CREATE TABLE Audit_MyRealTable(ColPK int, col1 int)

--You now need an auditing trigger for INSERT, UPDATE, DELETE. I prefer 1
trigger per action.

--Here is the update trigger

CREATE TRIGGER tr_u_MyRealTable ON MyRealTable FOR UPDATE
AS
INSERT Audit_MyRealTable(ColPK, col1)
SELECT ColPK, col1 FROM UPDATED
GO

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"L Phuong" <lp*****@neh.gov> wrote in message
news:40*********************@news.frii.net...

Allan, would you please show me how to do it in SQL Enterprise or in
VB6. I'm a novice in this subject.
Thank you.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!