Update dynamic SQL table with history table

Question

I was successful in implementing a table's history (for auditing) based on this answer that basically suggests to create two tables wherein one contains the current version and the other a table of versions with the real data:

CREATE TABLE dbo.Page(  
    ID int PRIMARY KEY,  
    Name nvarchar(200) NOT NULL,  
    CreatedByName nvarchar(100) NOT NULL, 
    CurrentRevision int NOT NULL, 
    CreatedDateTime datetime NOT NULL

CREATE TABLE dbo.PageContent(
    PageID int NOT NULL,
    Revision int NOT NULL,
    Title nvarchar(200) NOT NULL,
    User nvarchar(100) NOT NULL,
    LastModified datetime NOT NULL,
    Comment nvarchar(300) NULL,
    Content nvarchar(max) NOT NULL,
    Description nvarchar(200) NULL

My UPDATEs to static, non-dynamic fields are OK where the versioning is recognised correctly (rough pseudocode adapted from dev't code):

$sql_pagecontent = array(
    'PageID' => // logic to get PageID
    'Title' => $data['Title']
    'User' => $data['User'],
    'LastModified' => $this->get_date(),
    'Comment' => $data['Comment'],
    'Content' => $data['Content'],
    'Description' => $data['Description']
);

$this->db->insert('PageContent', $sql_pagecontent);
$id_version = $this->db->insert_id();

$sql_page = array(
    'CurrentRevision' => $id_version
);

if($type_version === 'UPDATE')
{
    $this->db->where('ID', $page_id_to_replace);
    $this->db->update('Page', $sql_page);
}
else
{
    $this->db->insert('Page', $sql_page);
}

The problem is when I apply this to dynamic tables with UPDATE.

During UPDATE, I want to achieve the following, just similar to the non-dynamic tables (context of Page and PageContent):

• a new PageContent row is INSERTed (new PageContent), a new row of CurrentRevision in Page is added
• a new PageContent row is INSERTed (update of existing PageContent), the CurrentRevision in Page UPDATEd with this new row
• deleted PageContents are removed from Page

NOTE I want each row of these dynamic tables preserved in both Page and PageContent if ever the user wants to check the changes so I just cannot do INSERT which will just flood the table.

I cannot seem to filter these dynamic values correctly with the current implementation.

Please advise on how to deal with this use case.

---

Solution

I am not sure if my current implementation of my said requirements is correct, feasible or recommended but it does the job:

1. get ID of current PageContent being modified
2. get ID of current Page being modified
3. delete all rows of Page that has the ID of #1
4. perform INSERT and apply UPDATE as type of action of PageContent

Number 3 is key to make sure whether existing rows are edited/deleted or new rows added are saved as the current version in Page. Number 4 puts this into place to make sure every row is recognised.