473,543 Members | 2,282 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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
3 9291
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******@msson line.net> wrote in message
news:73******** *************** ***@posting.goo gle.com...
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
[posted and mailed, please reply in news]

Bill Tepe (bi******@msson line.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_insert ed FROM inserted
select * INTO #tblname_delete d 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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
10756
by: Aaron C | last post by:
Hi, I'm trying to do an insert with the following statement: INSERT INTO user VALUES ( 'ag@ag.com','ag','Aaron','Chandler','','','La Mirada','CA',90638,714,'',''); and I'm getting the error message "Column count doesn't match value count at row 1".
3
21692
by: David M | last post by:
When using a dataset, how can one get to a column data element using this format: dataset.Tables.Rows.... I can get to a specific row, but I am not sure how to get to the column or data. I tried using the keyword Column, but no luck. Also, is direct access the best way to access this data, or should I be using different DataRows, etc....
0
2522
by: adjuster11 | last post by:
Bear with me, Noob question. I'm not a programmer, but a proprietary software program, Xactimate 24 property estimate, is forcing me to be one. I've tried multiple installs of their program on a new AMD 3200+ with 64 bit capable, but 32 bit as the operating setting, and XP Pro 32 bit as the OS. They blame my 64 bit capable computer, for...
0
1341
by: Paul | last post by:
Hello, I have a datagridview with a checkbox in a column, and I want to detect when the user change the value of the checkbox in the event CheckedChanged, but I only detect that the value is changed when the focus is in another column. An idea? Thanks
1
10457
by: jmarr02s | last post by:
I am trying to change my Amount column Data Type from Integer to Decimal (precision 9 digits, scale 3, that is 6 digits to the left of decimal and 3 digits to the right of decimal. Here is the error message I received SQL0443N Routine "SYSPROC.ALTOBJ" I sense this is a security issue, something my DBA must resolve and some aspect I do...
0
1429
by: Daniel | last post by:
how does ado.net SqlDataReader.GetString() know which encoding to read the data into a string as? Does sql sever set this at the column data type level, server wide encoding setting, os encoding?
1
1512
by: nanie | last post by:
i am new in vb. i would like to know the vb script for detect duplicate data entry. this is the system for booking bus.the bus have unique number.nobody can book the same bus.the system will give the message to the user if the bus is already book.
2
5751
by: bogie | last post by:
Hello I have some problem to change my column data type in my table property from character(15) to character(100). I use Postgresql 7.4. The problem is I try to make new column with charcater(100) then i copy the data from teh original column to the new column, then i try to drop the column but i get the following ERROR : Cannot drop table...
0
1573
by: Mark A | last post by:
Suppose a table like this: CREATE TABLE TABLE_A (COL_1 INT NOT NULL PRIMARY KEY, COL_2 CHAR(1) NOT NULL); and a SQL statement like this: UPDATE TABLE_A SET COL_1 = ? AND COL_2 =? WHERE COL_1 = ?;
0
7397
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7336
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7582
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7675
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5877
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
4884
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3384
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3385
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
0
626
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.