Solved

SQL Server 2005 row update

Posted on 2011-03-25
3
256 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

911 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

19 Experts available now in Live!

Get 1:1 Help Now