title: SQL merge multiple selects on table (trigger inserted)
body: <p>I am new to the forum and am looking for help with my SQL trigger.</p>
  
  <p>The trigger monitors the table tbl_adresse whether something has changed or a new data line has been added.</p>
  
  <p>My SQL trigger should collect data and write it in a new table.
  This works so far with this code:</p>
  
  <pre><code>CREATE TRIGGER [tgr_TEST] ON [dbo].[tbl_adresse]
  AFTER INSERT, UPDATE
  AS
  BEGIN
          MERGE [dbo].[WAWIDL_ADRESSKORREKTUR_RECHNUNGSADRESSE] AS WDL
          USING (SELECT d.* FROM tbl_adresse AS d JOIN inserted AS i ON i.kAdresse = d.kAdresse) AS WWW
              ON      TBL.XML_tadresse_kAdresse = WWW.kAdresse
              AND     TBL.XML_tadresse_kKundenID = WWW.kKundenID
          WHEN    MATCHED
              AND (
                      ISNULL(TBL.XML_tadresse_cFirma,0)           != ISNULL(WWW.cFirma,0) OR
                      ISNULL(TBL.XML_tadresse_cZusatz,0)          != ISNULL(WWW.cZusatz,0) OR
                      ISNULL(TBL.XML_tadresse_cTitel,0)           != ISNULL(WWW.cTitel,0) OR
                      ISNULL(TBL.XML_tadresse_cVorname,0)         != ISNULL(WWW.cVorname,0) OR
                      ISNULL(TBL.XML_tadresse_cName,0)            != ISNULL(WWW.cName,0) OR
                      ISNULL(TBL.XML_tadresse_cStrasse,0)         != ISNULL(WWW.cStrasse,0) OR
                      ISNULL(TBL.XML_tadresse_cPLZ,0)             != ISNULL(WWW.cPLZ,0) OR
                      ISNULL(TBL.XML_tadresse_cOrt,0)             != ISNULL(WWW.cOrt,0) OR
                      ISNULL(TBL.XML_tadresse_cAdressZusatz,0)    != ISNULL(WWW.cAdressZusatz,0)
                  )
          THEN
              UPDATE
                  SET
                      TBL.WDL_cKundenNr                           = '',
                      TBL.WDL_cBestellNr                          = '',
  
                      TBL.XML_tadresse_cFirma                     = ISNULL(WWW.cFirma,''),
                      TBL.XML_tadresse_cZusatz                    = ISNULL(WWW.cZusatz,''),
                      TBL.XML_tadresse_cTitel                     = ISNULL(WWW.cTitel,''),
                      TBL.XML_tadresse_cVorname                   = ISNULL(WWW.cVorname,''),
                      TBL.XML_tadresse_cName                      = ISNULL(WWW.cName,''),
                      TBL.XML_tadresse_cStrasse                   = ISNULL(WWW.cStrasse,''),
                      TBL.XML_tadresse_cPLZ                       = ISNULL(WWW.cPLZ,''),
                      TBL.XML_tadresse_cOrt                       = ISNULL(WWW.cOrt,''),
                      TBL.XML_tadresse_cAdressZusatz              = ISNULL(WWW.cAdressZusatz,'')
          WHEN    NOT MATCHED BY TARGET
          THEN
              INSERT  (
                      XML_tadresse_kAdresse,
                      XML_tadresse_kKundenID,
                      WDL_cKundenNr,
                      WDL_cBestellNr,
                      XML_tadresse_cFirma,
                      XML_tadresse_cZusatz,
                      XML_tadresse_cTitel,
                      XML_tadresse_cVorname,
                      XML_tadresse_cName,
                      XML_tadresse_cStrasse,
                      XML_tadresse_cPLZ,
                      XML_tadresse_cOrt,
                      XML_tadresse_cAdressZusatz
                      )
              VALUES  (
                      WWW.kAdresse,
                      WWW.kKundenID,
                      '',
                      '',
                      ISNULL(WWW.cFirma,''),
                      ISNULL(WWW.cZusatz,''),
                      ISNULL(WWW.cTitel,''),
                      ISNULL(WWW.cVorname,''),
                      ISNULL(WWW.cName,''),
                      ISNULL(WWW.cStrasse,''),
                      ISNULL(WWW.cPLZ,''),
                      ISNULL(WWW.cOrt,''),
                      ISNULL(WWW.cAdressZusatz,'')
                      );
  END
  GO
  </code></pre>
  
  <p>Unfortunately I am now missing more information, which I only get from a second table called tbl_orders.</p>
  
  <p>I have now tried to make another JOIN, but it does not work, I get the error message that the connection is already busy, there are probably 2 queries being made at the same time.</p>
  
  <p>"Die Verbindung ist mit Ergebnissen von einem anderen hstmt belegt."</p>
  
  <pre><code>CREATE TRIGGER [tgr_TEST] ON [dbo].[tbl_adresse]
  AFTER INSERT, UPDATE
  AS
  BEGIN
          MERGE [dbo].[WAWIDL_ADRESSKORREKTUR_RECHNUNGSADRESSE] AS WDL
          USING (SELECT d.* FROM tbl_adresse AS d JOIN inserted AS i ON i.kAdresse = d.kAdresse) AS WWW JOIN tbl_orders AS o ON WWW.kAdresse = o.kAdresse
              ON      TBL.XML_tadresse_kAdresse = WWW.kAdresse
              AND     TBL.XML_tadresse_kKundenID = WWW.kKundenID
          WHEN    MATCHED
              AND (
                      ISNULL(TBL.XML_tadresse_cFirma,0)           != ISNULL(WWW.cFirma,0) OR
                      ISNULL(TBL.XML_tadresse_cZusatz,0)          != ISNULL(WWW.cZusatz,0) OR
                      ISNULL(TBL.XML_tadresse_cTitel,0)           != ISNULL(WWW.cTitel,0) OR
                      ISNULL(TBL.XML_tadresse_cVorname,0)         != ISNULL(WWW.cVorname,0) OR
                      ISNULL(TBL.XML_tadresse_cName,0)            != ISNULL(WWW.cName,0) OR
                      ISNULL(TBL.XML_tadresse_cStrasse,0)         != ISNULL(WWW.cStrasse,0) OR
                      ISNULL(TBL.XML_tadresse_cPLZ,0)             != ISNULL(WWW.cPLZ,0) OR
                      ISNULL(TBL.XML_tadresse_cOrt,0)             != ISNULL(WWW.cOrt,0) OR
                      ISNULL(TBL.XML_tadresse_cAdressZusatz,0)    != ISNULL(WWW.cAdressZusatz,0)
                  )
          THEN
          UPDATE
              SET
                  TBL.WDL_cKundenNr                           = ISNULL(WWW.cKundenNr,''),
                  TBL.WDL_cBestellNr                          = ISNULL(WWW.cBestellNr,''),
                  TBL.XML_tadresse_cFirma                     = ISNULL(WWW.cFirma,''),
                  TBL.XML_tadresse_cZusatz                    = ISNULL(WWW.cZusatz,''),
                  TBL.XML_tadresse_cTitel                     = ISNULL(WWW.cTitel,''),
                  TBL.XML_tadresse_cVorname                   = ISNULL(WWW.cVorname,''),
                  TBL.XML_tadresse_cName                      = ISNULL(WWW.cName,''),
                  TBL.XML_tadresse_cStrasse                   = ISNULL(WWW.cStrasse,''),
                  TBL.XML_tadresse_cPLZ                       = ISNULL(WWW.cPLZ,''),
                  TBL.XML_tadresse_cOrt                       = ISNULL(WWW.cOrt,''),
                  TBL.XML_tadresse_cAdressZusatz              = ISNULL(WWW.cAdressZusatz,'')
      WHEN    NOT MATCHED BY TARGET
      THEN
          INSERT  (
                  XML_tadresse_kAdresse,
                  XML_tadresse_kKundenID,
                  WDL_cKundenNr,
                  WDL_cBestellNr,
                  XML_tadresse_cFirma,
                  XML_tadresse_cZusatz,
                  XML_tadresse_cTitel,
                  XML_tadresse_cVorname,
                  XML_tadresse_cName,
                  XML_tadresse_cStrasse,
                  XML_tadresse_cPLZ,
                  XML_tadresse_cOrt,
                  XML_tadresse_cAdressZusatz
                  )
          VALUES  (
                  WWW.kAdresse,
                  WWW.kKundenID,
                  ISNULL(WWW.cKundenNr,''),
                  ISNULL(WWW.cBestellNr,''),
                  ISNULL(WWW.cFirma,''),
                  ISNULL(WWW.cZusatz,''),
                  ISNULL(WWW.cTitel,''),
                  ISNULL(WWW.cVorname,''),
                  ISNULL(WWW.cName,''),
                  ISNULL(WWW.cStrasse,''),
                  ISNULL(WWW.cPLZ,''),
                  ISNULL(WWW.cOrt,''),
                  ISNULL(WWW.cAdressZusatz,'')
                  );
  </code></pre>
  
  <p>What am I doing wrong?
  Can't you add any more JOIN tables at INSERTED?</p>
  
  <p>Thank You!</p>