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]
GOI then added this to my data model and added 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 }
);