title: How can I made a trigger work with two source tables?
body: <p>I'm having trouble getting this trigger working correctly. I rarely work with triggers so my understanding of them may be a bit off because the way I have this coded seems like it should work (to me at least) but it does not. If an update is made to tblCollateralAssignment then the trigger works correctly and saves all the information in the tblCollateralAssignmentHistory table (including any information from the CustodianData table).</p>
  <p>But, if an update is made that ONLY touches the CustodianData table (for example, only updating the CustodianFrom field) and no field from tblCollateralAssignemnt is touched, then the trigger does not work and the update does not get saved to tblCollateralAssignmentHistory.</p>
  <p>Hoping I'm just overlooking something small but I've been staring at it and am at a loss.</p>
  <pre><code>ALTER TRIGGER [dbo].[Trigger_tblCollateralAssignmentToHistory]
     ON [dbo].[tblCollateralAssignment] AFTER UPDATE
  AS 
  
  SET NOCOUNT ON;
  
  IF EXISTS ( SELECT ChainID FROM inserted ) AND EXISTS ( SELECT ChainID FROM deleted )
      BEGIN
      
          --This updates history with the changed records.
          INSERT INTO dbo.tblCollateralAssignmentHistory
          SELECT i.ChainID
                ,d.LoanNo
                ,d.FileDate as BeginFileDate
                ,i.FileDate as EndFileDate
                ,d.ModifiedBy
                ,d.RecordSource
                ,d.AssignmentNo
                ,d.AssignmentFrom
                ,d.AssignmentTo
                ,d.RecordedDate
                ,d.StatusPerTitle
                ,d.RecordedImage
                ,d.AtCustodian
                ,d.AOMComments
                ,d.IsVisible
                ,d.DocXNotarized
                ,d.AOMBook
                ,d.AOMDocumentNo
                ,d.AOMPage
                ,d.CorrectiveAssignment
                ,d.DocumentID
                ,d.DocumentDetails
                ,cd.CustodianUID
                ,cd.CustodianDocCode AS DocCode
                ,d.MASIdentified
                ,cd.CustodianExceptionDescription AS ExceptionDescription
                ,cd.CustodianFrom
                ,cd.CustodianTo
                ,cd.CustodianNotation
                ,cd.CustodianComments
          FROM deleted as d
          JOIN inserted as i on i.ChainID = d.ChainID
          LEFT JOIN ExternalReports.CustodianData AS cd ON cd.ChainId = i.ChainID
      END
  
  --Clear out CorrectiveAssignment if the related ChainID IsVisible is set to false.  This is a trigger enacted cascade delete for soft deleted chains.  Edited records are also copied to history.
  DECLARE @FileDate datetime = GETDATE();
  UPDATE 
      dbo.tblCollateralAssignment
  SET 
      CorrectiveAssignment = NULL,
      FileDate = @FileDate
  OUTPUT  
      inserted.ChainID,
      deleted.LoanNo,
      deleted.FileDate AS BeginFileDate,
      @FileDate AS EndFileDate,
      deleted.ModifiedBy,
      deleted.RecordSource,
      deleted.AssignmentNo,
      deleted.AssignmentFrom,
      deleted.AssignmentTo,
      deleted.RecordedDate,
      deleted.StatusPerTitle,
      deleted.RecordedImage,
      deleted.AtCustodian,
      deleted.AOMComments,
      deleted.IsVisible,
      deleted.DocXNotarized,
      deleted.AOMBook,
      deleted.AOMDocumentNo,
      deleted.AOMPage,
      deleted.CorrectiveAssignment,
      deleted.DocumentID,
      deleted.DocumentDetails,
      cd.CustodianUID,
      cd.CustodianDocCode AS DocCode,
      deleted.MASIdentified,
      cd.CustodianExceptionDescription AS ExceptionDescription,
      cd.CustodianFrom,
      cd.CustodianTo,
      cd.CustodianNotation,
      cd.CustodianComments    
  INTO 
      dbo.tblCollateralAssignmentHistory
  FROM 
      dbo.tblCollateralAssignment AS CorrectiveAssignment
  LEFT JOIN
      ExternalReports.CustodianData AS cd ON cd.ChainId = CorrectiveAssignment.ChainID
  WHERE EXISTS 
      (select * from dbo.tblCollateralAssignment as DeletedAssignment where DeletedAssignment.IsVisible = 0 and DeletedAssignment.ChainID = CorrectiveAssignment.CorrectiveAssignment)
  AND 
      CorrectiveAssignment is not null
  AND EXISTS
      (select * from inserted AS i where i.LoanNo = CorrectiveAssignment.LoanNo);
  </code></pre>