History of object fields changes using database triggers

Managers often need to track changes in a document or an object. One of the ways of implementing this feature is using database triggers.

Trigger (database) is a stored procedure of a special type, which is not called by a user directly, but by an action that modifies data: adding (INSERT), deleting  (DELETE) a row in the specified table, or changing (UPDATE) data in a specific column of the database table.

Using a trigger allows avoiding additional load on the application server and is one of the best ways to save the history of adding/deleting/updating data in a table.

Consider an example of creating a trigger for changing the Taxpayer Number in the Contractor table and adding this information to another table. Before that, you need to create a table either in Microsoft SQL Management Studio with a CREATE TABLE query or in ELMA by creating a new object with the required properties.

 AFTER INSERT\UPDATE\DELETE – start the trigger after adding/updating/deleting data in an object.

INSERTED,DELETED – temporary tables, stored in the operative memory; used for checking the results of changing data and setting conditions for activating triggers.

//MS SQL
CREATE TRIGGER [dbo].[ChangeTPN]
   ON  [dbo].[Contractor]   
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    //Add data to the table with the following fields (Contractor name, Field name, New field value, Date of change)
    insert into  dbo.ObjectChangeHistory(Name,FieldName,FieldValue,
    ChangeDateTime)
    select  Name,’Taxpayer Number’, TPN , GETDATE() from INSERTED
END

In this example, upon any change and entry will be added to the change history table and the current value of Taxpayer Number will be written.

Since the trigger above processes any changes, a lot of instances will be generated, which is not convenient for tracking. Let's look at an example of a trigger that will process changes only in several fields and write it to the table.

//MS SQL
CREATE TRIGGER [dbo].[ContractorUpdate] ON [dbo].[Contractor]
AFTER UPDATE
AS
/**Declare variables**/
DECLARE
 
            @new_Name                   NVARCHAR (max)
        ,   @new_LegalAddress               NVARCHAR (max)
        ,   @new_TPN                        NVARCHAR (max)
        ,   @uid                        NVARCHAR (max)
 
        ,   @old_Name                   NVARCHAR (max)
        ,   @old_LegalAddress               NVARCHAR (max)
        ,   @old_TPN                        NVARCHAR (max)
 
BEGIN
SET NOCOUNT ON;
/**Assign values to variables**/
 SELECT @new_Name = Name,
        @new_LegalAddress = (SELECT Name FROM dbo.Address ad WHERE ins.LegalAddress = ad.id),
     @new_TPN = TPN,
     @uid = "uid"
 FROM   INSERTED  ins /**Changed table field values**/
 
 
SELECT @old_Name = Name,
        @old_LegalAddress = (SELECT Name FROM dbo.Address ad WHERE del.LegalAddress = ad.id),
     @old_TPN = TPN
 FROM   DELETED del    /**Old table field values**/
 
 
 /**Check whether the values of the selected fields had changed, if yes, write to the change history table**/
 IF @old_Name<>@new_Name
 BEGIN
    insert into dbo.ChangeHistoryMultFields (ContractorUID,FieldName,OldFieldName,NewFieldName,ChangeDate)
    values (@uid,’Name’,@old_Name,@new_Name,GETDATE())
 END
 
 IF @old_LegalAddress<>@new_LegalAddress
 BEGIN
    insert into dbo.ChangeHistoryMultFields (ContractorUID,FieldName,OldFieldName,NewFieldName,ChangeDate)
    values (@uid,’Legal address’,@old_LegalAddress,@new_LegalAddress,GETDATE())
 END
 
 IF @old_TPN<>@new_TPN
 BEGIN
    insert into dbo.ChangeHistoryMultFields (ContractorUID,FieldName,OldFieldName,NewFieldName,ChangeDate)
    values (@uid,’Taxpayer Number’,@old_TPN,@new_TPN,GETDATE())
 END
 SET NOCOUNT OFF;
END

In this example, changes in three fields (name, legal address, Taxpayer Number) are tracked, and if the fields had changed, then the information (contractor uid, field name, old field value, new field value, change date) is written to another table. Later you can add a form to this object and track the changes in the selected fields.

 

This is a solution for a specific database table. If you need a generic solution, you can use stored procedures, that will create the trigger body and apply it to the required table.

Here is a similar variant for Firebird.

//Firebird
CREATE TRIGGER tr_Update ON Contractor
AFTER UPDATE
AS
/**Declare variables**/
DECLARE VARIABLE    
            new_Name                    NVARCHAR (max);
            new_LegalAddress                NVARCHAR (max);
            new_TPN                     NVARCHAR (max);
            uid                     NVARCHAR (max);
 
            old_Name                    NVARCHAR (max);
            old_LegalAddress                NVARCHAR (max);
               old_TPN                      NVARCHAR (max);
 
BEGIN
/**Assign values to the variables**/
 SELECT new_Name into :Name,
        new_LegalAddress into :(SELECT Name FROM dbo.Address ad WHERE ins.LegalAddress = ad.id),
     new_TPN into :TPN,
     uid into :"uid"
 FROM   INSERTED  ins /**Change the values of the table fields**/
 
 
SELECT old_Name into :Name,
       old_LegalAddress into :(SELECT Name FROM dbo.Address ad WHERE del.LegalAddress = ad.id),
     old_TPN into :TPN
 FROM   DELETED del    /**Old table field values**/
 
 
 /**Check whether the values of the selected fields had changed, if yes, write to the change history table**/
 IF (old_Name<>new_Name)
 THEN   insert into dbo.ChangeHistoryMultFields (ContractorUID,FieldName,OldFieldName,NewFieldName,ChangeDate)
    values (uid,’Name’,old_Name,new_Name,GETDATE())
 
 IF (old_LegalAddress<>new_LegalAddress)
 THEN insert into dbo.ChangeHistoryMultFields (ContractorUID,FieldName,OldFieldName,NewFieldName,ChangeDate)
    values (uid,’Legal address’,old_LegalAddress,new_LegalAddress,GETDATE())
 
 IF (old_TPN<>new_TPN)
 THEN   insert into dbo.ChangeHistoryMultFields (ContractorUID,FieldName,OldFieldName,NewFieldName,ChangeDate)
    values (uid,’Taxpayer Number’,old_TPN,new_TPN,GETDATE())
END