I have a trigger on a table which is used to show the current and previous values of certain changed columns. my code in trigger is as follows.
ALTER TRIGGER [dbo].[AttendanceTrackingTrigger]
ON [dbo].[TimeDetails]
AFTER UPDATE
AS
if(UPDATE (TimeIn) or UPDATE(TimeOut) or UPDATE(ShiftID))
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @EmpNo nvarchar(100),@EmpName nvarchar(100),@FieldName nvarchar(100),@UserID int,@EmpID int,
@OldTimein smalldatetime,@NewTimein smalldatetime,@OldTimeout smalldatetime,@NewTimeout smalldatetime,
@OldShift nvarchar(100),@NewShift nvarchar(100),@ModifiedBy nvarchar(100)
SELECT @NewTimein=inserted.TimeIn from inserted
SELECT @OldTimein=deleted.TimeIn from deleted
SELECT @NewTimeout=inserted.TimeOut from inserted
SELECT @OldTimeout=deleted.TimeOut from deleted
--SELECT @OldShift=deleted.ShiftID from deleted
--SELECT @NewShift=inserted.ShiftID from inserted
SELECT @OldShift=deleted.ShiftID from deleted
SELECT @NewShift=inserted.ShiftID from inserted
SELECT @EmpName=inserted.UserName,@ModifiedBy=inserted.ModifedBy from inserted
SELECT @UserID=inserted.UserID from inserted
select @EmpID=EmpID from users where UserID=@UserID
Select @EmpNo =EmpNo from EmployeeMaster where EmpID=@EmpID
--Select SM.ShiftName as ShiftName from ShiftMaster SM join TimeDetails TD on TD.ShiftID=SM.ShiftID where UserID=@UserID
if(@NewTimein!=@OldTimein)
BEGIN
insert into TrackingofAttendanceChanges
--(EmpNo,EmpName,TableName,FieldName,PreviousValue,Currentvalue,ModifiedTime,ModifiedBy)
values (@EmpNo,@EmpName,'TimeDetails','TimeIn',@OldTimein,@NewTimein,Getdate(),@ModifiedBy)
END
if(@NewTimeout!=@OldTimeout)
BEGIN
insert into TrackingofAttendanceChanges
--(EmpNo,EmpName,TableName,FieldName,PreviousValue,Currentvalue,ModifiedTime,ModifiedBy)
values (@EmpNo,@EmpName,'TimeDetails','TimeOut',@OldTimeout,@NewTimeout,Getdate(),@ModifiedBy)
END
if(@NewShift!=@OldShift)
BEGIN
insert into TrackingofAttendanceChanges
values (@EmpNo,@EmpName,'TimeDetails','ShiftName',@OldShift,@NewShift,Getdate(),@ModifiedBy)
END
END
Here i am getting ShiftID but what I need is to show the Shift Name associated with ShiftID from the Shift master table.
With Regards,
Dinesh