Solved

SQL Server 2005 row update

Posted on 2011-03-25
3
284 Views
Last Modified: 2012-05-11
Experts,

We have a table with very large number of rows in our production, and users complain some information change every night a flag field gets set to a wrong value. We want to create a process to capture changed rows, and the time of each update. What would be the easiest to do so? Please explain.
Thanks,
Lynn
0
Comment
Question by:yrcdba7
3 Comments
 
LVL 16

Accepted Solution

by:
ToddBeaulieu earned 500 total points
ID: 35216516
Hi Lynn,

It sounds like you need a simple update trigger on the table. You'd probably want to make another table with the same schema and have the update trigger insert a copy of the new data, or even the old data.

Or, you could have it simply watch for a changing column or condition and record some message in a custom table.

It depends on what you really want to track and what you want to record.

Look up update triggers for a jumpstart. I'd be happy to help as you try to implement it.
0
 
LVL 18

Expert Comment

by:lludden
ID: 35216667
If you have a base table like this:
CREATE TABLE [dbo].[MyTable](
      [EntryID] [int] IDENTITY(1,1) NOT NULL,
      [EntryDate] [datetime] NOT NULL,
      [CustomerID] [int] NOT NULL,
      [ProductID] [int] NOT NULL
)
Then you can create a log table
CREATE TABLE [dbo].[MyTable_History](
      [EntryID] [int] IDENTITY(1,1) NOT NULL,
      [EntryDate] [datetime] NOT NULL,
      [CustomerID] [int] NOT NULL,
      [ProductID] [int] NOT NULL,
      [DateModified] [datetime] NOT NULL,
      [ModifiedBy] [varchar](30) NOT NULL
)

Then add a trigger on the base table:
CREATE TRIGGER [dbo].[MyTable_TrackChanges] ON [dbo].[MyTable]
FOR INSERT, UPDATE
AS

SET NOCOUNT ON

INSERT INTO MyTable_History  (EntryID, EntryDate, CustomerID, ProductID, DateModified, ModifiedBy)
SELECT    EntryID, EntryDate, CustomerID, ProductID,  getdate(), suser_sname()
FROM         inserted
0
 

Author Comment

by:yrcdba7
ID: 35429472
Thanks!
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question