Solved

SQL Server 2005 row update

Posted on 2011-03-25
3
295 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.

622 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