Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

SQL Server 2005 row update

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
yrcdba7
Asked:
yrcdba7
1 Solution
 
ToddBeaulieuCommented:
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
 
lluddenCommented:
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
 
yrcdba7Author Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now