by JBrooks
22. September 2014 06:30
I have all the changes archived to archive table using triggers, but this doesn’t record anything when a row is deleted. I created a dedicated table to record the deletes.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Deletes](
[id] [int] IDENTITY(1,1) NOT NULL,
[tableName] varchar(255) NOT NULL,
[deletedId] [int] NOT NULL,
[editBy] [varchar](50) NOT NULL,
[edited] [datetime] NOT NULL,
CONSTRAINT [PK_Deletes] PRIMARY KEY CLUSTERED
(
[id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Deletes] ADD CONSTRAINT [DF_Deletes_editBy]
DEFAULT (user_name()) FOR [editBy]
GO
ALTER TABLE [dbo].[Deletes] ADD CONSTRAINT [DF_Deletes_edited]
DEFAULT (getdate()) FOR [edited]
GO
I then added this to my data model and add a record to it right after I do a remove.
db.Areas.Remove(area);
db.Deletes.Add(new Delete() {
deletedId = id,
tableName = "Areas",
editBy = this.User.Identity.Name,
edited = DateTime.Now
});
2d4c9c80-5266-4811-8f5a-2e4eb33110c7|0|.0|27604f05-86ad-47ef-9e05-950bb762570c
Tags: