I am learning TSQL (SQL in general for that matter), and I wrote this trigger that inserts logs about updated employees into the
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;