Отслеживаем изменения в таблице с помощью триггеров Transact Sql

Задача: сохранять информацию о всех изменения в определенной таблицы. Наиболее простое решение - создать таблицу, совпадающую по структуре с отслеживаемой и при каждом изменении писать в эту таблицу строку с обновленным состоянием измененной строки. Сделать это можно с помощью триггера, отслеживающего операции INSERT, UPDATE и DELETE.

Ниже приведен код триггера для исходной таблицы людей PERSONS таблицы изменений CHANGES_PERSONS вида:

CREATE TABLE PERSONS
(
PERSON_ID INT NOT NULL IDENTITY PRIMARY KEY,
SURNAME VARCHAR(150) NOT NULL, 
NAME VARCHAR(150) NOT NULL,
OTCHESTVO VARCHAR(150) NOT NULL
)

CREATE TABLE CHANGES_PERSONS
(
CHANGE_ID_PERS int not null identity,
CHANGE_DATE datetime not null default getdate(),
CHANGE_TYPE varchar(10) not null,
PERSON_ID INT NOT NULL,
SURNAME VARCHAR(150) NOT NULL, 
NAME VARCHAR(150) NOT NULL,
OTCHESTVO VARCHAR(150) NOT NULL
) 

--- ТРИГГЕР ОТСЛЕЖИВАЮЩИЙ ИЗМЕНЕНИЯ
create trigger dbo.changes_persons_trigger
on  PERSONS FOR INSERT, UPDATE, DELETE NOT FOR REPLICATION
as
-- SET NOCOUNT ON добавлен чтобы не было лишних результатов выполнения операции
set NOCOUNT ON;

-- определеяем тип произошедших изменений INSERT,UPDATE, or DELETE 
declare @change_type as varchar(10)
declare @count as int
set @change_type = 'inserted' 
select @count = COUNT(*) FROM DELETED
if @count > 0
begin
    set @change_type = 'deleted' 
    select @count = COUNT(*) from INSERTED
    if @Count > 0
        set @change_type = 'updated' 
end
        
-- обработка удаления
if @change_type = 'deleted'
begin
    insert into CHANGES_PERSONS(CHANGE_TYPE, PERSON_ID, SURNAME, NAME, OTCHESTVO) select 'deleted', PERSON_ID, SURNAME, NAME, OTCHESTVO from deleted
end
else
begin
-- триггер не различает вставку и удаление, так что добавим ручную обработку
-- обработка вставки
    if @change_type = 'inserted'
    begin
        insert into HANGES_PERSONS(CHANGE_TYPE, PERSON_ID, SURNAME, NAME, OTCHESTVO) select 'inserted', PERSON_ID, SURNAME, NAME, OTCHESTVO from inserted
    end
-- обработка обновления
    else
    begin
        insert into HANGES_PERSONS(CHANGE_TYPE, PERSON_ID, SURNAME, NAME, OTCHESTVO) select 'updates', PERSON_ID, SURNAME, NAME, OTCHESTVO from inserted
    end
end -- завершение if
-- завершение dbo.changes_persons

Дополнительно:

  1. Track DML Changes Using after Trigger for Update, Delete and Insert rows- Capture Changed data rows using T-sql
  2. Use the inserted and deleted Tables
  3. CREATE TRIGGER (Transact-SQL)
  4. SET NOCOUNT (Transact-SQL)

Комментарии

Отслеживаем изменения в таблице с помощью триггеров Transact Sql — Комментарии (3)

  1. Скажите, можно ли в триггере получить название процедуры, которая внесла изменения в таблицу. OBJECT_NAME(@@PROCID) - выдает название триггера, а нужно узнать, кем были сделаны изменения

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

Добавить комментарий для polina Отменить ответ

Ваш e-mail не будет опубликован. Обязательные поля помечены *


*

Можно использовать следующие HTML-теги и атрибуты: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>