Solved

SQL Server 2005 row update

Posted on 2011-03-25
3
248 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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks!
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now