SQL Server triggers - insert new rows into audit table

Question

I have a simple table as below.

I want to create a trigger to insert new values into "SectionsAudit" Table.

Means:

1. If a new row is inserted into the Sections table, I want to insert the same row into the Audit table
2. If an existing row is updated in the Sections table, I want to create a new row in the Audit table with the updated row.

How can I do that in SQL Server? Also, I would like to know if this a good practice?

CREATE TABLE [dbo].[Sections]  
(
    [Id]               int IDENTITY(1,1) NOT NULL,
    [Name]             varchar(25) NOT NULL,
    [InsertedBy]       varchar(25) NOT NULL,
    [InsertedDateTime] datetime NOT NULL,
    [UpdatedBy]        varchar(25) NOT NULL,
    [UpdatedDateTime]  datetime NOT NULL,

    CONSTRAINT [PK_Id] PRIMARY KEY CLUSTERED([Id])
) 

Answer 1

The trigger will look something like this, its a good practice depending of the MS SQL version you're using. Note that for using this approach is necessary to have in your dbo.SectionsAudit a field to track the ModificationId of the original Record (in the example I called it IdModi)

CREATE TRIGGER [dbo].[tr_SectionsAudit]
    ON  [dbo].[Sections]
    AFTER INSERT, UPDATE
    NOT FOR REPLICATION
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    INSERT      SectionsAudit
                (Id, IdModi, fieldname, ...)
    SELECT      Id, IdModi = (ISNULL((SELECT    ISNULL(MAX(SA.IdModi), 0)
                                                    FROM    SectionsAudit SA 
                                                    WHERE   SA.Id = I.Id
                                                    ) , 0) + 1),
                fieldname, ...
    FROM        INSERTED I 
    

END

Answer 2

In your case I suggest you to use Rowversion or Change Data Capture. Below some option to do it:

Rowversion

It is a unique binary numbers of 8 bytes. It is updated every time that you insert or update any column of any records

Example:

CREATE TABLE dbo.Test (ID int PRIMARY KEY, RowVersion rowversion) ;

Change Data Capture

Change data capture records insert, update, and delete activity that is applied to a SQL Server table. This makes the details of the changes available in an easily consumed relational format. Column information and the metadata that is required to apply the changes to a target environment is captured for the modified rows and stored in change tables that mirror the column structure of the tracked source tables. Table-valued functions are provided to allow systematic access to the change data by consumers.

Temporal Tables

Start from SQLServer 2016 you can use the system-versioned temporal table that is a type of user table designed to keep a full history of data changes and allow easy point in time analysis. This type of temporal table is referred to as a system-versioned temporal table because the period of validity for each row is managed by the system (i.e. database engine). Every temporal table has two explicitly defined columns, each with a datetime2 data type. These columns are referred to as period columns. These period columns are used exclusively by the system to record period of validity for each row whenever a row is modified.

Example:

CREATE TABLE dbo.test
(
   [ID] int NOT NULL PRIMARY KEY CLUSTERED
   ,[ValidFrom] datetime2 GENERATED ALWAYS AS ROW START
   ,[ValidTo] datetime2 GENERATED ALWAYS AS ROW END
   ,PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo))
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TestHistory));

Triggers

Creates a DML, DDL, or logon trigger. A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server. DML triggers run when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event fires, whether table rows are affected or not.