title: What ways can I have my database create an archive entry when a table is updated or a row deleted?
body: <p>I have a simple table in my SQL Server 2012 database</p>
  
  <pre><code>CREATE TABLE [dbo].[Content] (
      [ContentId]       INT            IDENTITY (1, 1) NOT NULL,
      [SubjectId]       INT            NOT NULL,
      [Title]           NVARCHAR (50)  NOT NULL,
      [Text]            NVARCHAR (MAX) NOT NULL,
      [Version]         ROWVERSION     NOT NULL,
      [CreatedBy]       INT            NOT NULL,
      [CreatedDate]     DATETIME       NOT NULL,
      [ModifiedBy]      INT            NOT NULL,
      [ModifiedDate]    DATETIME       NOT NULL,
      CONSTRAINT [PK_Content] PRIMARY KEY CLUSTERED ([ContentId] ASC)
  );
  GO
  CREATE NONCLUSTERED INDEX [Content_Subject_IX]
      ON [dbo].[Content]([SubjectId] ASC);
  GO
  </code></pre>
  
  <p>When a user edits or deletes data in this table then all changes are lost. What I would really like is for the changes to be retained somehow. </p>
  
  <p>Can anyone give me any suggestions or point me to a possible solution. I open to any kind of suggestion and would welcome some ideas. For example would it be reasonable to create an archive table and if I did that then how could I populate that table from inside the database. Is there a simple way that I could do this with triggers? </p>
  
  <p>I hope someone can point me in the right direction. I have no DBA to ask for suggestions so I would appreciate any advice.</p>