yrcdba7
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!
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_TrackChange
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