History Tables in SQL

create_entity.sql

CREATE TABLE entity (
  id INTEGER NOT NULL AUTO_INCREMENT
  ,createdDate TIMESTAMP NOT NULL
  ,createdBy INTEGER NOT NULL
  ,deletedDate TIMESTAMP NULL
  ,status VARCHAR(50) NOT NULL
  ,ownerId INTEGER NOT NULL -- Foreign key to user that currently owns entity
  ,companyId INTEGER NOT NULL -- Foreign key to company that owns entity... never changes
)

create_entity_history.sql

CREATE TABLE entity_history (
  id INTEGER NOT NULL
  
  -- Versioning specific fields:
  ,effectiveDate TIMESTAMP NOT NULL -- When was this change effective in the real world?
  ,transactionDate TIMESTAMP NOT NULL -- When was this change recorded in the database?
  ,deletedDate TIMESTAMP NOT NULL -- When was this entity (not entity version) deleted?
  ,effectedBy INTEGER NOT NULL -- App user that made the change
  ,transactedBy VARCHAR(50) NOT NULL -- Database user that made the change
  ,transactionType VARCHAR(10) NOT NULL -- Type of change (create / update / delete)
  
  -- Fields we want to version
  ,status VARCHAR(50) NOT NULL
  ,ownerId INTEGER NOT NULL -- Foreign key to user that currently owns entity
  
  -- Fields we don't care to version
  ,companyId INTEGER NOT NULL -- Foreign key to company that owns entity... never changes
  
  CONSTRAINT PK_entity PRIMARY KEY (
    id,
    effectiveDate,
    transactionDate
  )
)

db_triggers.sql

DROP TRIGGER IF EXISTS entity_after_insert;
DROP TRIGGER IF EXISTS entity_after_update;

CREATE TRIGGER entity_after_insert AFTER INSERT ON entity FOR EACH ROW BEGIN
  INSERT INTO entity_history
    SELECT
      e.id
      ,e.createdDate AS effectiveDate
      ,CURRENT_TIMESTAMP() AS transactionDate
      ,e.deletedDate
      ,e.createdBy AS effectedBy
      ,SESSION_USER() AS transactedBy
      ,'INSERT' AS transactionType
      ,e.status
      ,e.ownerId
    FROM entity AS e WHERE e.id = NEW.id;
END;

CREATE TRIGGER entity_after_update AFTER UPDATE ON entity FOR EACH ROW BEGIN
  INSERT INTO entity_history
    SELECT
      e.id
      ,CURRENT_TIMESTAMP() AS effectiveDate
      ,CURRENT_TIMESTAMP() AS transactionDate
      ,e.deletedDate
      ,e.createdBy AS effectedBy
      ,SESSION_USER() AS transactedBy
      ,'UPDATE' AS transactionType
      ,e.status
      ,e.ownerId
    FROM entity AS e WHERE e.id = NEW.id;
END;