SQL trigger to log when employee records are updated
First some comments.

Usage of Deprecated Features

Currently your trigger does a SELECT that returns a resultset from a trigger. That was at one time supported but has been since deprecated since at least 2012. I would remove them.

Audit requirement

I very much agree with @Austin Hasting that it's generally better to not transform the data into a concatenated string here. You lose the ability to query or filter. The user-friendly formatting should be left to the application or at least the stored procedures used by the application. It doesn't belong in the trigger.

Bad assumptions 

Your trigger assumes that you will have only one row in inserted and deleted. That is not how T-SQL triggers will work. It works on the statement level, not row level. Therefore you should code your trigger as if it could contain 10 rows in both virtual tables. The way the code is, it may not necessarily error out but it may miss out on other rows because the assignments only operate on the last row being assigned. Please, don't do this row by agonizing row. 

Lack of two-part naming

It's a very good habit to always reference to your objects with two-part names, &lt;schema&gt;.&lt;object&gt; and not just &lt;object&gt;, because an object can exist in several schema with same names, and everyone has their default schema which takes precedence over the schema you actually meant to. I'm assuming you are using the default dbo schema. At least qualify that. 

Lack of statement terminator

Several features have been added to the T-SQL language to a point where the ; terminator are no longer totally optional. T-SQL has been an aberrant in the SQL standard in making it optional. Some hack their way by putting a ; in front of statement whenever it's required, which is very weird. The best reason to use one everywhere is because it makes for more readable code, and makes your intention explicit.

Lack of block

I really do not like triggers or stored procedure without an explicit BEGIN...END block. It is valid T-SQL to write without one but this has problems and can make for confusing scripting. 

Usage of + operator

You are concatenating strings with the + operator. Without knowing the version of your SQL Server, it might be the only choice but I should point out that since 2012, you now have a CONCAT() function which is much better because it will handle the nulls whereas + will propagate nulls and thus leave you with.... a null. 

Lack of explicit insert list

When doing an INSERT, and especially when you do have an explicit SELECT list, it's usually good to have an explicit list. Otherwise, schema change could break the query. 

With the commentary out of the way, this is how I'd rewrite this.

ALTER TRIGGER tr_tblEmployee_ForUpdate
ON dbo.tblEmployee
FOR UPDATE AS
BEGIN
  WITH Updated AS (
    SELECT
      i.Name,
      i....
      d.Name AS OldName,
      d.... AS Old...
    FROM inserted AS i
    INNER JOIN deleted AS d
      ON i.&lt;primary key&gt; = d.&lt;primary key&gt;
  )
  INSERT INTO dbo.tblEmployeeAudit2 (&lt;Name of column&gt;)
  SELECT CONCAT(
    'Employee with ID = ',
    u.ID,
    ' changed Name from ', u.Name ' to ', u.OldName
    ...
  ) AS AuditDescription
  FROM Updated AS u;
END;