[ELMA3] История изменений полей объекта с использованием триггеров в БД

В работе руководителя или другого ответственного лица часто возникает необходимость отслеживать изменения в каком-либо документе или справочнике. Одним из вариантов реализации этого являются триггеры в базе данных.

Триггер (база данных) – это хранимая процедура особого типа, которую пользователь не вызывает непосредственно, а исполнение которой обусловлено действием по модификации данных: добавлением INSERT, удалением DELETE строки в заданной таблице, или изменением UPDATE данных в определенном столбце заданной таблицы реляционной базы данных.

Использование триггера позволяет дополнительно не нагружать сервер приложения и является одним из лучших способов ведения аудита (запись истории) добавления/удаления/изменения данных в таблице.

Внимание!

Одним из минусов работы с триггерами является то, что при обновлении системы они пропадают, поэтому необходимо в ручном режиме запускать повторное создание триггеров путем запуска заранее сохранённого запроса через менеджер БД.

Рассмотрим простой пример создания триггера на изменение поля ИНН в таблице Контрагент и добавление этой информации в другую. Перед этим необходимо создать таблицу либо в Microsoft SQL Management Studio с помощью запроса CREATE TABLE или через ELMA путем создания нового объекта с необходимыми свойствами.

AFTER INSERT\UPDATE\DELETE – запуск триггера после добавления\обновления\удаления данных в объекте.

INSERTED,DELETED – временные таблицы, находящиеся в оперативной памяти, используются для проверки результатов изменений данных и для установки условий срабатывания триггеров.

//MS SQL
CREATE TRIGGER [dbo].[ChangeINN]
   ON  [dbo].[Contractor]   
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    //Добавляем данные в таблицу cо следующими полями (Название контрагента, Название поля, Новое значение поля, Дата изменения)
    insert into  dbo.IstoriyaRabotySObjektami(Naimenovanie,NazvaniePolya,ZnacheniePolya,
    DataVremyaIzmeneniya)
    select  Name,’ИНН’, INN , GETDATE() from INSERTED
END

В данном примере при любом изменении будет добавляться запись в таблицу истории изменений и будет записываться текущее значение ИНН.

Так как представленный выше триггер срабатывает на любое изменение, то будет формироваться много записей, которые будет неудобно отслеживать. Поэтому рассмотрим пример триггера, который будет обрабатывать изменение только нескольких полей и записывать это в таблицу.

//MS SQL
CREATE TRIGGER [dbo].[ContractorUpdate] ON [dbo].[Contractor]
AFTER UPDATE
AS
/**Объявляем переменные**/
DECLARE
 
            @new_Name                   NVARCHAR (max)
        ,   @new_LegalAddress               NVARCHAR (max)
        ,   @new_INN                        NVARCHAR (max)
        ,   @uid                        NVARCHAR (max)
 
        ,   @old_Name                   NVARCHAR (max)
        ,   @old_LegalAddress               NVARCHAR (max)
        ,   @old_INN                        NVARCHAR (max)
 
BEGIN
SET NOCOUNT ON;
/**Присваиваем значение переменным**/
 SELECT @new_Name = Name,
        @new_LegalAddress = (SELECT Name FROM dbo.Address ad WHERE ins.LegalAddress = ad.id),
     @new_INN = INN,
     @uid = "uid"
 FROM   INSERTED  ins /**Измененные значения полей таблицы**/
 
 
SELECT @old_Name = Name,
        @old_LegalAddress = (SELECT Name FROM dbo.Address ad WHERE del.LegalAddress = ad.id),
     @old_INN = INN
 FROM   DELETED del    /**Старые значения полей таблицы**/
 
 
 /**Проверяем изменилось ли значение выбранных полей, если да то записываем в таблицу истории изменений**/
 IF @old_Name<>@new_Name
 BEGIN
    insert into dbo.IstoriyaIzmeneniyaNeskoljkoP (UidKontragenta,NazvaniePolya,StaroeZnacheniePolya,NovoeZnacheniePolya,DataIzmeneniya)
    values (@uid,’Наименование’,@old_Name,@new_Name,GETDATE())
 END
 
 IF @old_LegalAddress<>@new_LegalAddress
 BEGIN
    insert into dbo.IstoriyaIzmeneniyaNeskoljkoP (UidKontragenta,NazvaniePolya,StaroeZnacheniePolya,NovoeZnacheniePolya,DataIzmeneniya)
    values (@uid,’Юридический адрес’,@old_LegalAddress,@new_LegalAddress,GETDATE())
 END
 
 IF @old_INN<>@new_INN
 BEGIN
    insert into dbo.IstoriyaIzmeneniyaNeskoljkoP (UidKontragenta,NazvaniePolya,StaroeZnacheniePolya,NovoeZnacheniePolya,DataIzmeneniya)
    values (@uid,’ИНН’,@old_INN,@new_INN,GETDATE())
 END
 SET NOCOUNT OFF;
END

В данном примере мы отслеживаем изменение трех полей (название, юр.адрес, ИНН), и если значение поля изменилось, то записываем информацию (uid контрагента, название поля, старое значение поля, новое значение поля, дату изменения) в другую таблицу. В дальнейшем можно навесить на этот объект форму и отслеживать изменение выбранных полей.

Мы рассмотрели создание триггера на конкретную таблицу БД, если же нужно универсальное решение, то можно использовать хранимые процедуры, которые будут формировать тело триггера и применять его к нужной таблице.

В заключение рассмотрим аналогичный вариант в БД Firebird.

//Firebird
CREATE TRIGGER tr_Update ON Contractor
AFTER UPDATE
AS
/**Объявляем переменные**/
DECLARE VARIABLE    
            new_Name                    NVARCHAR (max);
            new_LegalAddress                NVARCHAR (max);
            new_INN                     NVARCHAR (max);
            uid                     NVARCHAR (max);
 
            old_Name                    NVARCHAR (max);
            old_LegalAddress                NVARCHAR (max);
               old_INN                      NVARCHAR (max);
 
BEGIN
/**Присваиваем значение переменным**/
 SELECT new_Name into :Name,
        new_LegalAddress into :(SELECT Name FROM dbo.Address ad WHERE ins.LegalAddress = ad.id),
     new_INN into :INN,
     uid into :"uid"
 FROM   INSERTED  ins /**Измененные значения полей таблицы**/
 
 
SELECT old_Name into :Name,
       old_LegalAddress into :(SELECT Name FROM dbo.Address ad WHERE del.LegalAddress = ad.id),
     old_INN into :INN
 FROM   DELETED del    /**Старые значения полей таблицы**/
 
 
 /**Проверяем изменилось ли значение выбранных полей, если да то записываем в таблицу истории изменений**/
 IF (old_Name<>new_Name)
 THEN   insert into dbo.IstoriyaIzmeneniyaNeskoljkoP (UidKontragenta,NazvaniePolya,StaroeZnacheniePolya,NovoeZnacheniePolya,DataIzmeneniya)
    values (uid,’Наименование’,old_Name,new_Name,GETDATE())
 
 IF (old_LegalAddress<>new_LegalAddress)
 THEN insert into dbo.IstoriyaIzmeneniyaNeskoljkoP (UidKontragenta,NazvaniePolya,StaroeZnacheniePolya,NovoeZnacheniePolya,DataIzmeneniya)
    values (uid,’Юридический адрес’,old_LegalAddress,new_LegalAddress,GETDATE())
 
 IF (old_INN<>new_INN)
 THEN   insert into dbo.IstoriyaIzmeneniyaNeskoljkoP (UidKontragenta,NazvaniePolya,StaroeZnacheniePolya,NovoeZnacheniePolya,DataIzmeneniya)
    values (uid,’ИНН’,old_INN,new_INN,GETDATE())
END