[posted and mailed, please reply in news]
Bill Tepe (bi******@mssonline.net) writes:
I have a need to insert rows into an Audit type table when values
change in certain fields in a table. I thought I could do this via a
trigger. However, on requirement is to include in the audit both the
old and new value.
In a trigger you can retrieve the new value in the "inserted" table
and the old value in the "deleted" tables. These tables are virtual
and are accessible only in the trigger.
Beware that a trigger in SQL Server fires once per statement, not once
per row as in some other products. Thus, the tables can old many rows.
You should also be aware of access to these tables when they contain
many rows can be slow. Therefore it is often good idea to start a trigger
with:
select * INTO #tblname_inserted FROM inserted
select * INTO #tblname_deleted FROM deleted
Since you are into auditing... If you are doing this on any large
scalce, you should probably consider third-party solutions rather
than reinventing the wheel.
www.redmatrix.com has a product SQLAudit,
which I have no experience of myself.
--
Erland Sommarskog, SQL Server MVP,
so****@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp