Tracking data changes


Sooner or later, it happens. Someone asks “When this data has been modified? Who changed this information?”

A reliable system must ensure this information could be obtained any time.

In this post I offer you a simple way to add columns to any table you want to preserve the metadata about the last changes.

In another post, I’ll talk about tracking all the changes.

Like in the last posts, I prepare it as stored procedure, which receives two parameters:

  • The Table Name
  • The Schema Name (optional) in which case, “dbo” is assumed

The procedure will add the following columns:

Column  NameInformation StoredDefault Value
Created byusername in the insert actionCURRENT_USER
Modified Byusername in the update action 
Creation Datedate & time of the creationSYSUTCDATETIME
Modification Datedate & time of the update 
Note: An “Active” column is added as well, to manage “soft deletes” in case you need it.

Notice there is no default value for update actions, since they are not supported by the database. The values must be provided by application procedures. In fact, they could be provided for the creation process as well, if desired.

Some code highlights

Using a select from INFORMATION_SCHEMA.COLUMNS the procedure detects if the column to add already exists.

DECLARE 
   @Isthere INT;
DECLARE 
   @CheckForColumn NVARCHAR(300) =
   'select @Isthere =count(*) from INFORMATION_SCHEMA.COLUMNS T where TABLE_SCHEMA=@schema and TABLE_NAME=@TableName and T.COLUMN_NAME=@ColumnName'

Like in other examples I already post, using sp_executesql stored procedure, can execute the T-SQL sentence passing parameters.

DECLARE 
   @ColumnName SYSNAME = 'Created by';
EXEC [sp_executesql] 
       @CheckForColumn
 ,
 N'@Schema sysname,@TableName sysname,@ColumnName sysname,@Isthere int OUTPUT'
 , @schema
 , @TableName
 , @ColumnName
 , @Isthere OUTPUT;

If the column doesn’t exist, two other dynamic T-SQL sentences are executed, adding the column and later configuring the default value.

DECLARE 
   @SqlToExecute NVARCHAR(MAX);
IF @Isthere = 0
  BEGIN
    SET @SqlToExecute =
                        'ALTER TABLE ['
                        + @schema
                        + '].['
                        + @TableName
                        + '] ADD ['
                        + @columnName
                        + '] nvarchar(150) NULL';
    EXEC [sp_executesql] 
           @SqlToExecute;
    SET @SqlToExecute =
                        'ALTER TABLE ['
                        + @schema
                        + '].['
                        + @TableName
                        + '] ADD CONSTRAINT [DF_'
                        + @TableName
                        + '_'
                        + @columnName
                        + '] DEFAULT CURRENT_USER FOR ['
                        + @columnName
                        + ']';
    EXEC [sp_executesql] 
           @SqlToExecute;
  END;

The same steps are repeated for the other columns.

In similar way with other previous posts, this stored procedure could be executed over all the tables in a database, using a select from INFORMATION_SCHEMA.TABLES.

You can find the procedure in my repository here.

HTH

Comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Salir /  Cambiar )

Google photo

Estás comentando usando tu cuenta de Google. Salir /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Salir /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Salir /  Cambiar )

Conectando a %s

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios .