sql server (alternate): 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.

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


[quoted text, click to view]

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 ***

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


[quoted text, click to view]