How to find out which field in a table is updated

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 helpThen 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" <lphuong@.neh.gov> wrote in message
news:b6a732a8.0404300532.30de1e3c@.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|||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!|||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" <lphuong@.neh.gov> wrote in message
news:4092936d$0$202$75868355@.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!