Is it possible to add some metadata (like username, ...) into history table of temporal table?
For example if I have below table
-- Create a Temporal Table
CREATE TABLE dbo.Sample
(
SampleId int identity(1,1) PRIMARY KEY CLUSTERED
, SampleDate date NOT NULL
-- Other fields...
, SysStartTime datetime2 GENERATED ALWAYS AS ROW START
, SysEndTime datetime2 GENERATED ALWAYS AS ROW END
, PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.SampleHistory));
I want to know how to insert some new record to this table with some additional metadata store in SampleHistory for example something like below
INSERT INTO [dbo].[Sample]
([SampleDate]
,[SampleHistory].[Editor])
VALUES
('13/07/1400'
,'soroshsabz')