In this case, you would do better with three triggers rather than only one.

On your AFTER INSERT trigger, you know every column was changed. You have the option of logging each item into the table separately, or entering a special row where FieldEffected could equal INSERT, ALL (INSERT), or some other specific value you choose. OldData would be NULL, and newData would be something like:

Cast(@@Identity as varchar(10) ) + '|' + IsNull(i.ClientName, '') + '|' + Cast(i.gender as char(1)) + '|' + .... Until you have all of the fields appended into one string. This is simple. However, having one item per field is cleaner, so you may decide to simply explicitly code each separate insert into the trigger. An almost identical procedure could be created for a AFTER DELETE trigger as well.

It only gets complex on an update trigger.

There are various ways that you could try to automate the trigger so that you would always include each field in the table. Iterating through the correct system view would let you automatically generate the INSERT statements. However, for this example, I am assuming fixed code for the query.

There are two primary ways to identify if a column has changed (three, if you count a direct compare between INSERTED and DELETED). The simplest to understand is the UPDATE() function, e.g.

IF UPDATE(ClientName)
... add logging code here ...

IF UPDATE(Gender)
....

Note that the documentation indicates that UPDATE() will return TRUE even if the update failed (that is, nothing changed).

There is also the COLUMNS_UPDATED() function, that returns a bit pattern indicating which columns changed (if any). This returns a VARBINARY containing enough bits so that there is one bit per column in the table. If you performed an UPDATE on the sample table snippet:

000 -- There are only five fields, so the last three bits per byte are useless
0 -- Status not updated
1 -- address updated
0 -- gender not updated
1 -- Client Name updated
0 -- Normally, you will not be updating the ID

However you determine it, when you identify the changed columns, you then do a simple INSERT into your log with the appropriate data.

Note that the documentation indicates that UPDATE() will return TRUE even if the update failed (that is, nothing changed).
It will always be true if the column participated in an UPDATE statement, regardless of whether the value changed or not. So really the only way to truly know if the value changed is to compare the values in Deleted and Inserted.

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…