-- Setup tables and sample data
CREATE TABLE #HR_employees (
employee_code INT PRIMARY KEY,
firstname VARCHAR(20),
surname VARCHAR(20),
department VARCHAR(15),
email VARCHAR(200)
)

CREATE TABLE #Payroll_employees (
employee_code INT PRIMARY KEY ,
firstname VARCHAR(20),
surname VARCHAR(20),
department VARCHAR(15),
email VARCHAR(200)
)

CREATE TABLE #log (
id INT IDENTITY(1,1) PRIMARY KEY,
Date_of_change DATETIME NOT NULL,
Field VARCHAR(20) NOT NULL,
OldValue VARCHAR(40) NOT NULL,
NewValue VARCHAR(40) NOT NULL
)

INSERT INTO #HR_employees (employee_code, firstname, surname, department, email)
VALUES (1, 'Dave', 'Smith', 'IT', 'dave.smith@email.com'),
	(2, 'John', 'Smith', 'Admin', 'john.smith@email.com'),
	(3, 'Luke', 'Donald', 'IT', 'luke.donald@email.com'),
	(4, 'Rob', 'Hurst', 'IT', 'rob.hurst@email.com'),
	(5, 'Garry', 'Hilburt', 'IT', 'garry.hilburt@email.com')
	
INSERT INTO #Payroll_employees(employee_code, firstname, surname, department, email)
VALUES (1, 'Dave', 'Smith', 'IT', 'dave.smith@email.com'),
	(2, 'John', 'Smith', 'Admin', 'john.smith@email.com'),
	(3, 'Luke', 'Donald', 'IT', 'luke.donald@email.com'),
	(4, 'rob', 'Smith', 'IT', 'rob.smith@email.com'),
	(5, 'Harry', 'Hilburt', 'IT', 'harry.hilburt@email.com')
	
-- Cursor	
DECLARE @HR_employee_code INT,
	@HR_firstname VARCHAR(20),
	@HR_surname VARCHAR(20),
	@HR_department VARCHAR(15),
	@HR_email VARCHAR(200),
	@Payroll_firstname VARCHAR(20),
	@Payroll_surname VARCHAR(20),
	@Payroll_department VARCHAR(15),
	@Payroll_email VARCHAR(200)
	
DECLARE update_employees CURSOR

FOR 

SELECT HR.employee_code,
	HR.firstname,
	HR.surname,
	HR.department,
	HR.email,
	Payroll.firstname,
	Payroll.surname,
	Payroll.department,
	Payroll.email
FROM #HR_employees AS HR
INNER JOIN #Payroll_employees AS Payroll ON Payroll.employee_code = HR.employee_code

OPEN update_employees

FETCH NEXT FROM update_employees INTO
	@HR_employee_code, 
	@HR_firstname,
	@HR_surname, 
	@HR_department, 
	@HR_email, 
	@Payroll_firstname,
	@Payroll_surname, 
	@Payroll_department, 
	@Payroll_email 
	
WHILE @@FETCH_STATUS = 0
	BEGIN
	
	IF @Payroll_firstname != @HR_firstname
		BEGIN
		
		UPDATE #Payroll_employees 
		SET firstname = @HR_firstname
		WHERE #Payroll_employees.employee_code = @HR_employee_code
		
		INSERT INTO #log (Date_of_change, Field, OldValue, NewValue)
			VALUES (GETDATE(), 'Firstname', @Payroll_firstname, @HR_firstname)
		
		END
		
	IF @Payroll_surname != @HR_surname
		BEGIN
		
		UPDATE #Payroll_employees 
		SET surname = @HR_surname
		WHERE #Payroll_employees.employee_code = @HR_employee_code
		
		INSERT INTO #log (Date_of_change, Field, OldValue, NewValue)
			VALUES (GETDATE(), 'Surname', @Payroll_surname, @HR_surname)
		
		END
		
	-- You get the idea
	
	FETCH NEXT FROM update_employees INTO
	@HR_employee_code, 
	@HR_firstname,
	@HR_surname, 
	@HR_department, 
	@HR_email, 
	@Payroll_firstname,
	@Payroll_surname, 
	@Payroll_department, 
	@Payroll_email 
	END
	
CLOSE update_employees
DEALLOCATE update_employees	

SELECT * FROM #log

DROP TABLE #HR_employees
DROP TABLE #Payroll_employees
DROP TABLE #log

ALTER TRIGGER upd_PayrollEmployee
ON Payroll_employees
AFTER update
AS
	IF COLUMNS_UPDATED() & 2 = 2
		INSERT mylog (Date_of_change, Field, OldValue, NewValue)
		SELECT GETDATE(), 'firstname', d.firstname, i.firstname
		FROM inserted i JOIN deleted d on i.employee_code = d.employee_code
						AND d.firstname <> i.firstname
		
	IF COLUMNS_UPDATED() & 4 = 4
		INSERT mylog (Date_of_change, Field, OldValue, NewValue)
		SELECT GETDATE(), 'surname', d.surname, i.surname
		FROM inserted i JOIN deleted d on i.employee_code = d.employee_code
						AND d.surname <> i.surname
		
	IF COLUMNS_UPDATED() & 8 = 8
		INSERT mylog (Date_of_change, Field, OldValue, NewValue)
		SELECT GETDATE(), 'department', d.department, i.department
		FROM inserted i JOIN deleted d on i.employee_code = d.employee_code
						AND d.department <> i.department
	
	IF COLUMNS_UPDATED() & 16 = 16
		INSERT mylog (Date_of_change, Field, OldValue, NewValue)
		SELECT GETDATE(), 'email', d.email, i.email
		FROM inserted i JOIN deleted d on i.employee_code = d.employee_code
						AND d.email <> i.email
	
GO

UPDATE Payroll_employees  SET 
	firstname = h.firstname, surname = h.surname, department =h.department , email=h.email
FROM HR_employees h
WHERE Payroll_employees.employee_code = h.employee_code
and (Payroll_employees.firstname <> h.firstname
	OR Payroll_employees.surname <> h.surname
	OR Payroll_employees.department <> h.department
	OR Payroll_employees.email <> h.email)

Update 
  dbo.Employees
Set 
  NameLast = @NameLast,
  NameFirst = @NameFirst
Output 
  deleted.EmployeeNumber, 
  deleted.NameLast, 
  deleted.NameFirst
Into dbo.LogTable (
  EmployeeNumber, 
  OldNameLast, 
  OldNameFirst)
Where 
  EmployeeNumber = @EmployeeNumber

DECLARE @MyTableVar table(
    employee_code int NOT NULL,
	Date_of_change DATETIME NOT NULL,
    firstname_new VARCHAR(20),
    firstname_old VARCHAR(20),
    surname_new VARCHAR(20),
    surname_old VARCHAR(20)
    );
    
UPDATE #Payroll_employees 
SET 
	firstname = HR.firstname,
	surname = HR.surname
OUTPUT 
	INSERTED.employee_code,
	GETDATE(),
	INSERTED.firstname,
	DELETED.firstname,
	INSERTED.surname,
	DELETED.surname
INTO @MyTableVar
FROM #HR_employees AS HR
INNER JOIN #Payroll_employees AS Payroll ON Payroll.employee_code = HR.employee_code
WHERE
(
	Payroll.firstname != HR.firstname
	OR Payroll.surname != HR.surname
)

INSERT INTO #log (Date_of_change, Field, OldValue, NewValue)
SELECT  
--	employee_code,
	Date_of_change,
	ColName AS ColumnName,
	CASE WHEN ColName = 'Firstname' AND firstname_old != firstname_new
		 THEN firstname_old 
		 ELSE surname_old 
		 END AS OldValue,
	CASE WHEN ColName = 'Firstname' AND firstname_old != firstname_new
		 THEN firstname_new 
		 ELSE surname_new 
		 END AS OldValue
FROM    @MyTableVar MTV
CROSS APPLY
(
	SELECT 'Firstname' AS ColName UNION ALL 
	SELECT 'Surname'
)sub
WHERE 
(
	ColName = 'Firstname' AND firstname_old != firstname_new
	OR
	ColName = 'Surname' AND surname_old != surname_new
)