Как создать таблицу истории?

#sql #sql_server

                    
Есть таблица Persons:

| ID | Name  | Post       |
| 1  | Kolin | manager    |
| 2  | Emma  | specialist |


Нужно создать копию таблицы - Persons_history котарая отслеживает изменений.  

| ID | Name  | Post       | created_at                  | created_by | Operation |   
+----+-------+------------+-----------------------------+------------+-----------+
| 1  | Kolin | manager    | 2015-08-06 10:04:28.6000000 | hh\Mark    | Insert    |
| 2  | Emma  | specialist | 2015-08-17 17:55:03.6600000 | hh\Mark    | Update    | 


К примеру таблица должна выглядит так.
Как можно реализовать? Как создать таблицу истории?
    

Ответы

Ответ 1

Создаём таблички:

IF OBJECT_ID('Persons')IS NOT NULL DROP TABLE Persons
IF OBJECT_ID('Persons_History')IS NOT NULL DROP TABLE Persons_History
GO

CREATE TABLE Persons(
  Id INT IDENTITY(1,1),
  name VARCHAR(255),
  post VARCHAR(255)
)

GO

CREATE TABLE Persons_History(
  Id INT,
  name VARCHAR(255),
  post VARCHAR(255),
  modify_at DATETIME,
  modify_by NVARCHAR(255),
  Operation VARCHAR(6)
)
GO


Создание триггеров(можно обойтись и одним на самом деле)

CREATE TRIGGER Persons_History_Trigger_Insert
ON Persons AFTER INSERT AS
INSERT Persons_History SELECT Id, name, post, GETDATE(), SUSER_SNAME(), 'insert' 
FROM INSERTED
GO
CREATE TRIGGER Persons_History_Trigger_Update
ON Persons AFTER UPDATE AS
INSERT Persons_History SELECT Id, name, post, GETDATE(), SUSER_SNAME(), 'update' 
FROM INSERTED
GO
CREATE TRIGGER Persons_History_Trigger_Delete
ON Persons AFTER DELETE AS
INSERT Persons_History SELECT Id, name, post, GETDATE(), SUSER_SNAME(), 'delete' 
FROM DELETED
GO


DML операции и вывод результата

INSERT Persons
VALUES('Kolin', 'manager'),('Emma','specialist')

UPDATE Persons SET name='pegoopik' WHERE id=1

DELETE FROM Persons

SELECT *
FROM Persons_History


Ну и результат:

Id          name       post       modify_at               modify_by             
        Operation
----------- ---------- ---------- ----------------------- ------------------------------
---------
2           Emma       specialist 2016-01-22 12:12:44.157 ALPHA\XXX-Krasovskiy-EA
       insert
1           Kolin      manager    2016-01-22 12:12:44.157 ALPHA\XXX-Krasovskiy-EA
       insert
1           pegoopik   manager    2016-01-22 12:12:44.160 ALPHA\XXX-Krasovskiy-EA
       update
2           Emma       specialist 2016-01-22 12:12:44.160 ALPHA\XXX-Krasovskiy-EA
       delete
1           pegoopik   manager    2016-01-22 12:12:44.160 ALPHA\XXX-Krasovskiy-EA
       delete


Можно ещё добавить, что вместо VARCHAR(6) для поля Operation можно хранить код операции,
например, в byte. 0-insert; 1-update; 2-delete. Чуть сэкономит место.