SQL trigger to log when employee records are updated I am learning TSQL (SQL in general for that matter), and I wrote this trigger that inserts logs about updated employees into the tblEmployeeAudit table.

When I was looking at the end result my eyes hurt.
The trigger was super simple, yet super long (for something which is so simple), it seemed as if a 10-year-old rascal wrote it. Does anyone know if I can make this any better/shorter, and/or How to avoid repeating same line of code 100 times? Here's the code.

Alter TRIGGER tr_tblEmployee_ForUpdate
ON tblEmployee
FOR UPDATE

AS
declare @id int
declare @Name nvarchar(50)
declare @GenderID int
declare @Salary int
declare @City nvarchar(50)
declare @DepartmentID int
declare @ManagerID int
declare @DateOfBirth datetime

declare @Nameol nvarchar(50)
declare @GenderIDol int
declare @Salaryol int
declare @Cityol nvarchar(50)
declare @DepartmentIDol int
declare @ManagerIDol int
declare @DateOfBirthol datetime  

BEGIN
    Select @id = employeeid from inserted

    Select @Name = Name 
          ,@GenderID = GenderID
          ,@Salary = Salary
          ,@City = City
          ,@DepartmentID = DepartmentID
          ,@ManagerID = ManagerID
          ,@DateOfBirth = DateOfBirth
    From Inserted

    Select @Nameol = Name 
          ,@GenderIDol = GenderID
          ,@Salaryol = Salary
          ,@Cityol = City
          ,@DepartmentIDol = DepartmentID
          ,@ManagerIDol = ManagerID
          ,@DateOfBirthol = DateOfBirth
    From deleted

    Select * from deleted
    Select * from inserted
    Insert into tblEmployeeAudit2 values(
    'Employee with ID =' 
    + str(@id) 
    + ' changed Name from ' + cast(@Nameol as nvarchar(50)) + ' to ' + cast(@Name as nvarchar(50) )
    + 'Gender from ' +cast(@GenderIDol as nvarchar(5)) +' to ' +cast(@GenderID as nvarchar(5))
    + 'Salary from ' +cast(@SalaryOl as nvarchar(5)) +' to ' +cast(@Salary as nvarchar(5))
    + 'City from ' +cast(@Cityol as nvarchar(50)) +' to ' +cast(@City as nvarchar(50))
    + 'Department from ' +cast(@DepartmentIDol as nvarchar(5)) +' to ' +cast(@DepartmentID as nvarchar(5))
    + 'ManagerID from ' +cast(@ManagerIDol as nvarchar(5)) + ' to ' +cast(@ManagerID as nvarchar(5))
    )

END;
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;