USE [Northwind_GAH];
GO

DROP TABLE [dbo].[ChangeLog];
GO

CREATE TABLE [dbo].[ChangeLog]
(
[ChangeID]	INT				IDENTITY(1,1) PRIMARY KEY,
[EmpID]		INT				NULL,
[User]		NVARCHAR(30)	NOT NULL,
[Date]		SMALLDATETIME	NOT NULL,
[OldRate]	MONEY			NULL,
[NewRate]	MONEY			NOT NULL
);
GO

ALTER TABLE [dbo].[Employees] ADD
[PayRate] MONEY NOT NULL DEFAULT 8.00;
GO

CREATE TRIGGER payChange ON Employees FOR UPDATE
AS
	IF UPDATE (PayRate)
	BEGIN
		DECLARE @emp_id		INT,
				@user		NVARCHAR(30),
				@date		SMALLDATETIME,
				@oldrate	MONEY,
				@newrate	MONEY

		SELECT @emp_id = inserted.EmployeeID
		FROM inserted
		SELECT @user = SUSER_SNAME()
		SELECT @date = GETDATE()
		SELECT @oldrate = deleted.PayRate
		FROM deleted
		SELECT @newrate = inserted.PayRate
		FROM inserted

		INSERT INTO ChangeLog(EmpID, [User], [Date], [OldRate], [NewRate])
		VALUES (@emp_id, @user, @date, @oldrate, @newrate)

	END;
GO

DROP TRIGGER payChange