Problem statement

Sometimes a database may have a separate table to log certain activities taking place in the database. Create a new table named ChangeLog in the Northwind database as follows:

ChangeID int identity(1,1) primary key EmpID int (will contain the ID of the employee being changed) ChangedBy nvarchar(30) (will contain the login of the user making the change) DateChanged smalldatetime (will contain the date of the change) OldRate money (will contain the old payrate of the employee) NewRate money (will contain the new payrate of the employee)

If you havent already done so (as this was part of the first Northwind project in Assn02), you also need to Alter the Employees table and add a PayRate Column and set a pay rate for the employees.

After creating the ChangeLog table, create a trigger on the Employees table for UPDATE. Use this trigger to monitor the PayRate column, and if the PayRate of an employee gets changed, have the trigger insert a row into the ChangeLog table with the appropriate data. [Use the function suser_sname() to get the user making the change and insert this into the user column.]