Question

Use the Northwind database. Choose the best answer from those given below.

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)
User          nvarchar(30)           (will contain the login of the user making the change)
Date          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)

You also need to Alter the Employees table and add a PayRate Column and set a pay rate for the employees.
CREATE TRIGGER payrate_change ON Employees FOR UPDATE
As
If UPDATE (PayRate) ...
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.]

Why is the given solution wrong:
CREATE TRIGGER payrate_change ON [dbo].[Employees]
FOR UPDATE
As
declare @empid int
declare @user nvarchar(50)
declare @date smalldatetime
declare @oldRate money
declare @newRate money
select @empid = e.EmployeeID from [dbo].[Employees] as e;
select @oldRate = e.PayRate from [dbo].[Employees] as e;
If UPDATE(PayRate)
BEGIN
set @user = SUSER_SNAME();
set @date = GETDATE();
select @newRate = e.PayRate from [dbo].[Employees] as e;
insert into [dbo].[ChangeLog](EmpID,[User],[Date],OldRate,NewRate)
values (@empid, @user, @date, @oldRate, @newRate)
END

Based upon the trigger problem from above, pick the correct solution(s) below. (Can be several)

A -

CREATE TRIGGER payrate_change ON Employees FOR UPDATE
AS
DECLARE @EmpID int;
DECLARE @User nvarchar(30);
DECLARE @Date smalldatetime;
DECLARE @OldRate money;
DECLARE @NewRate money;
IF UPDATE (PayRate)
BEGIN
SELECT @OldRate = (SELECT PayRate FROM DELETED);
SELECT @NewRate = (SELECT Payrate FROM INSERTED);
SELECT @EmpID = (SELECT EmployeeID FROM INSERTED);
SELECT @User = suser_sname();
SELECT @Date = getdate();  
INSERT INTO ChangeLog(EmpID, [User], [Date], OldRate, NewRate)
VALUES (@EmpID, @User, @Date, @OldRate, @NewRate);
END

B -

create Trigger payrate_change ON Employees FOR UPDATE
As
Declare @EmplID int
Declare @user nvarchar(30)
Declare @date smalldatetime
Declare @OldRate money
Declare @NewRate money
Select @EmplID=Employees.employeeID from employees
Select @user = suser_sname()
Select @date = getdate()
Select @oldrate = payrate from deleted
Select @newrate = payrate from inserted
If UPDATE (PayRate)
Insert into ChangeLog
( EmplID , [User] , [Date] , OldRate , NewRate )
Values ( @EmplID, @user, @date, @OldRate, @NewRate)

C -

CREATE Trigger payrate_change
ON Employees FOR UPDATE
As
DECLARE @OldRate money
DECLARE @NewRate money
DECLARE @EmpID int
If UPDATE (PayRate)
BEGIN
SELECT @OldRate - (SELECT PayRate FROM Deleted)
SELECT @NewRate - (SELECT PayRate FROM Inserted)
SELECT @EmpID - (SELECT EmployeeID FROM Inserted)
INSERT INTO ChangeLog (EmpID, User, OldRAte, NewRate)
VALUES (@EmpID, suser_sname(), @OldRate, @NewRate)
END;
ELSE
BEGIN
PRINT 'Commands executed successfully'
END

D -

CREATE TRIGGER payrate_change ON Employees FOR UPDATE
AS
DECLARE @OldRate money
DECLARE @NewRate money
DECLARE @EmpID = int
IF UPDATE (PayRate)
BEGIN
SELECT @OldRate = (SELECT PayRate FROM Deleted)
SELECT @NewRate = (SELECT PayRate FROM Inserted)
SELECT @EmpID = (SELECT EmployeeID FROM Employees WHERE EmpID = Deleted.EmpID )  
INSERT INTO ChangeLog (EmpID, [User], [Date], NewRate, OldRate)
VALUES (@EmpID, suser_sname(), getdate(), @OldRate, @NewRate)
END;
ELSE
BEGIN
PRINT 'Commands executed successfully'
END;

E -

CREATE TRIGGER payrate_change ON Employees FOR UPDATE
AS
DECLARE @OldRate money
DECLARE @NewRate money
DECLARE @EmpID int
IF UPDATE (PayRate)
BEGIN
SELECT @OldRate = (SELECT PayRate FROM Deleted)
SELECT @NewRate = (SELECT PayRate FROM Inserted)
SELECT @EmpID = (SELECT EmployeeID FROM Employees WHERE 1 =1)  
INSERT INTO ChangeLog (EmpID, [User], [Date], NewRate, OldRate)
VALUES (@EmpID, suser_sname(), getdate(), @OldRate, @NewRate)
END;
ELSE
BEGIN
PRINT 'Commands executed successfully'
END;

Answer 1

create table changelog(ChangeID int, identity number(1,1) primary key, EmpID int, User nvarchar(30), Date smalldatetime, OldRate money, NewRate money);

alter table employees add(PayRate int);

update employees set Payrate = 100;

CREATE TRIGGER payrate_change ON Employees FOR UPDATE
As
Declare
begin
insert into changelog values(:old.cid, :old.identity, :oldempid, :old.user, :old.date, :old.oldrate, :old.newrate);

end;