Есть таблица 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 |
К примеру таблица должна выглядит так.
Как можно реализовать? Как создать таблицу истории?
Ответ
Создаём таблички:
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. Чуть сэкономит место.
