SQL Server Trigger
Hello, I need your help in writing a Trigger that will fire when a new record is inserted in the TIMESHEET table. The idea is to also include with each record the current pay rate of the employee creating the new record in the timesheet. The current pay rate for each employee is locate in the USERS table and is called PayRate. The TIMESHEET and the USERS tables both includes the UserID. The trigger should only update the new inserted record. I guess it should be something like:
CREATE TRIGGER GetRate
ON dbo.TIMESHEET
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE dbo.TIMESHEET
SET TIMESHEET.RATE = USERID.PAYRATE
WHERE TIMESHEET.USERID = USERS.USERID
END
GO
Thank you all!
CREATE TRIGGER GetRate
ON dbo.TIMESHEET
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE dbo.TIMESHEET
SET TIMESHEET.RATE = USERID.PAYRATE
WHERE TIMESHEET.USERID = USERS.USERID
END
GO
Thank you all!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is there an "identity" column in the Timesheet table, or some other column that can be used to uniquely identify any given row?
ASKER
Yes, it is called RecordID.
Then add "and where i.RecordId = TimeSheet.RecordId" right after the current "where" clause"
correction...
Then add "and i.RecordId = TimeSheet.RecordId" right after the current "where" clause"
Then add "and i.RecordId = TimeSheet.RecordId" right after the current "where" clause"
ASKER
For everyone benefit, this is the final working solution:
update dbo.TimeSheet
set TimeSheet.Rate = u.PayRate
from inserted i, dbo.Users u
where i.EmployeeID = u.Userid and i.RecordID = TimeSheet.RecordID
Thank you all.
update dbo.TimeSheet
set TimeSheet.Rate = u.PayRate
from inserted i, dbo.Users u
where i.EmployeeID = u.Userid and i.RecordID = TimeSheet.RecordID
Thank you all.
ASKER