[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