Help with Trigger dealing with Inserted and Deleted Tables.

Posted on 2007-07-25
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:
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]

IF UPDATE (Manager)

      update [Code]
      set  [Code].[Supervisor] = (SELECT Inserted.[Manager] FROM INSERTED)
      where [Branch].BranchID = [Code].BranchID AND
      [Code].[Supervisor] = (SELECT Deleted.[Manager] FROM DELETED)


Any suggestions?
Question by:mathew_s
    LVL 50

    Accepted Solution

    CREATE TRIGGER [UpdateManager] ON [dbo].[Branch]

    IF UPDATE (Manager)
          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


    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...
    LVL 4

    Expert Comment

    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)
    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)
    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
    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

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now