By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,190 Members | 1,503 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,190 IT Pros & Developers. It's quick & easy.

How to detect if column data changed and know prev. and new value

P: n/a
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.

Is there a "simple" way to do this? I know I could query the table
before the update and compare to what the new value is and react
accordingly.

Just wondering if there is something nifty in Sql Server that I am
missing that could help me with this.

Thanks in advance for your help.

Bill
Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Hi

Check out CREATE TRIGGGER in Books Online or at
http://msdn.microsoft.com/library/de...asp?frame=true

In particular the COLUMNS_UPDATED example of the IF UPDATE clause.

John

"Bill Tepe" <bi******@mssonline.net> wrote in message
news:73**************************@posting.google.c om...
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.

Is there a "simple" way to do this? I know I could query the table
before the update and compare to what the new value is and react
accordingly.

Just wondering if there is something nifty in Sql Server that I am
missing that could help me with this.

Thanks in advance for your help.

Bill

Jul 20 '05 #2

P: n/a
[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
Jul 20 '05 #3

P: n/a
If you need to do this at more of an enterprise level, you might look
into Lumigent's Entegra (haven't used it but buying it next year :))

http://lumigent.com/products/entegra/entegra.htm

HTH

Ray Higdon MCSE, MCDBA, CCNA

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

This discussion thread is closed

Replies have been disabled for this discussion.