CREATE TABLE actualData (ID INT NOT NULL, Col1 VARCHAR(10), Col2 VARCHAR(10))
CREATE TABLE historicalData (ID INT NOT NULL, Date DATETIME DEFAULT GETDATE(), Col1 VARCHAR(10), Col2 VARCHAR(10))

CREATE TRIGGER trg_actualData ON actualData FOR UPDATE
AS
INSERT INTO historicalData (ID, Col1, Col2) SELECT Id, Col1, Col2 FROM deleted

INSERT INTO actualData VALUES (1, 'test', '1rst')
UPDATE actualData SET Col2 = '2nd' WHERE ID = 1
UPDATE actualData SET Col2 = '3rd' WHERE ID = 1

SELECT * FROM actualData
SELECT * FROM historicalData

CREATE TABLE actualData1 (ID INT NOT NULL, Col1 VARCHAR(10), Col2 VARCHAR(10))
CREATE TABLE historicalData1 (ID INT NOT NULL, Date DATETIME DEFAULT GETDATE(), Col1 VARCHAR(10), Col2 VARCHAR(10))

CREATE TRIGGER trg_actualData1 ON actualData1 FOR UPDATE
AS
MERGE historicalData1 AS trg
USING (SELECT ID, Col1, Col2 FROM deleted) AS src (ID, Col1, Col2) ON src.ID = trg.ID
WHEN MATCHED THEN
	UPDATE SET trg.Col1 = src.Col1, trg.Col2 = src.Col2
WHEN NOT MATCHED BY TARGET THEN
	INSERT (Id, Col1, Col2) VALUES (ID, Col1, Col2);


INSERT INTO actualData1 VALUES (1, 'test', '1rst')
UPDATE actualData1 SET Col2 = '2nd' WHERE ID = 1
UPDATE actualData1 SET Col2 = '3rd' WHERE ID = 1

SELECT * FROM actualData1
SELECT * FROM historicalData1