?
Solved

Help with Trigger dealing with Inserted and Deleted Tables.

Posted on 2007-07-25
2
Medium Priority
?
466 Views
Last Modified: 2008-01-09
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?
0
Comment
Question by:mathew_s
2 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 19565743
CREATE TRIGGER [UpdateManager] ON [dbo].[Branch]
FOR UPDATE
AS

IF UPDATE (Manager)
BEGIN
      update [Code]
           set  [Supervisor] = x.newmanager
         From Code as C
         Inner Join (select  i.branchid,I.manager as newmanager,d.manager as oldmanager
                           from Inserted as I
                           Inner join Deleted as D
                               on I.branchid=d.branchid -- i assume branchid is the primary key .....
                           Where i.manager <> d.manager and not (i.manager is null and d.manager is null)
                        ) as x
             on c.supervisor  = x.oldmanager
           and c.branchid=x.branchid
END

return


1) you always need to code trigger to deal with multiple updates from the source statements...
2) the If Update(column) clause just indicates that the column could have been updated by the source statement not that the data in the column has actually changed...
0
 
LVL 4

Expert Comment

by:bamboo7431
ID: 19567542
You could do that, but I'd suggest changing your design. Apparently your Supervisor field can have two meanings: actual person ("Peter Connor") or person having a certain position (Branch Manager of Branch A)
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, ManagerID)
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.EmployeeID
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
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question