Отслеживаем изменения в таблице с помощью триггеров 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

)

view sourceprint?

--- ТРИГГЕР ОТСЛЕЖИВАЮЩИЙ ИЗМЕНЕНИЯ

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

Ссылка на источник