mathew_s
asked on
Help with Trigger dealing with Inserted and Deleted Tables.
I need some help to see if this can be done in a trigger. Basically I want a cascading update where if the user changes the Manager in Branch table that the Supervisor field will change in the Code table as long as the branch id is the same as the Branch table and the Supervisor in the Code table was the same as the recently updated Manager that was updated in the Branch table.
The following may be useful:
TEST DATA:
1) Have a branch table with:
Branch=Branch A
Manager = John Smith
2) Have 2 codes in the Code table where branchid points to Branch A for both codes and the Supervisor field = John Smith for one code but for the other code the Supervisor field is Peter Connor.
When the user updates the Manager in the Branch table, I want the Supervisor field for John Smith to update to the new value but the Supervisor Peter Connor should not change. The Code and Branch tables are joined by the branchid field.
Can this be done using a trigger? I took some attempts but was not successful. Below was my last attempt... Thanks for any help!
CREATE TRIGGER [UpdateManager] ON [dbo].[Branch]
FOR UPDATE
AS
IF UPDATE (Manager)
BEGIN
update [Code]
set [Code].[Supervisor] = (SELECT Inserted.[Manager] FROM INSERTED)
where [Branch].BranchID = [Code].BranchID AND
[Code].[Supervisor] = (SELECT Deleted.[Manager] FROM DELETED)
END
Any suggestions?
The following may be useful:
TEST DATA:
1) Have a branch table with:
Branch=Branch A
Manager = John Smith
2) Have 2 codes in the Code table where branchid points to Branch A for both codes and the Supervisor field = John Smith for one code but for the other code the Supervisor field is Peter Connor.
When the user updates the Manager in the Branch table, I want the Supervisor field for John Smith to update to the new value but the Supervisor Peter Connor should not change. The Code and Branch tables are joined by the branchid field.
Can this be done using a trigger? I took some attempts but was not successful. Below was my last attempt... Thanks for any help!
CREATE TRIGGER [UpdateManager] ON [dbo].[Branch]
FOR UPDATE
AS
IF UPDATE (Manager)
BEGIN
update [Code]
set [Code].[Supervisor] = (SELECT Inserted.[Manager] FROM INSERTED)
where [Branch].BranchID = [Code].BranchID AND
[Code].[Supervisor] = (SELECT Deleted.[Manager] FROM DELETED)
END
Any suggestions?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Consider adding an extra field to your employees table that would signify that the record is not a real person, but a branch manager position(default it to NULL for real people). But then have an extra record that will have "Branch A Manager" for a name and branch ID for the new field. You could build a view that will retrieve you the proper person name.
Here's what I mean:
Branch(BranchID,BranchName
Data: 1, "Branch A", 1
Employee (EmployeeID, EmployeeName, BranchID)
Data:
1,"John Smith",NULL
2,"Peter Connor",NULL
3,"Branch A Manager",1 --this is the record for the branch manager
Code(CodeID,BranchId, SupervisorID)
Data:
1,1,2 --Peter Connor's project
2,1,3 --project that belongs to the manager of Branch A
--SELECT to retrieve the proper supervisor (could be turned into a view)
SELECT c.CodeID, b.BranchName,
CASE WHEN e.BranchID IS NULL THEN e.EmployeeName ELSE m.EmployeeName END AS Supervisor
FROM
Code c
INNER JOIN Branch b ON c.BranchID=b.BranchID
INNER JOIN Employee e ON c.SupervisorID=e.EmployeeI
LEFT OUTER JOIN ManagersBranch mb ON e.BranchID=mb.BranchID
INNER JOIN Employee m ON mb.EmployeeID=m.EmployeeID
This way you don't need a trigger and once you changed the ManagerID field in the Branch table the supervisor's names will change automatically