SQL Trigger für INSERT und UPDATE scheitert, weil INSERTED in Unterabfrage mehr als einen Wert liefert
Hallo zusammen,
ich habe eine bestehende Tabelle1, die ich per Trigger überwachen möchte.
Es sollen INSERT und UPDATE überwacht werden, so dass die Daten in einer weiteren Tabelle2 (KUNDENADRESSE) gesichert werden.
Ich frage also im Trigger zuerst ab, ob die Datenzeile bereits existiert, wenn nicht INSERT in Tabelle2 (KUNDENADRESSE), ansonsten Tabelle2 nur UPDATE.
Wenn ich jetzt auf Tabelle1 (tAdresse) einen UPDATE Befehl absetze und mehr als eine Zeile betroffen ist,
dann erscheint Meldung 512 mit "Die Unterabfrage hat mehr als einen Wert zurückgegeben.".
Ich dachte, dass INSERTED alle ankommenden Datenzeilen verarbeitet, kann aber wohl doch nur mit einer Zeile umgehen.
Habe nur etwas on "CURSOR" gelesen, aber hatte diese Funktion noch nie genutzt.
Wie müsste ich denn den Trigger umbauen, dass mehrere INSERTED Zeilen verarbeitet werden können?
ich habe eine bestehende Tabelle1, die ich per Trigger überwachen möchte.
Es sollen INSERT und UPDATE überwacht werden, so dass die Daten in einer weiteren Tabelle2 (KUNDENADRESSE) gesichert werden.
Ich frage also im Trigger zuerst ab, ob die Datenzeile bereits existiert, wenn nicht INSERT in Tabelle2 (KUNDENADRESSE), ansonsten Tabelle2 nur UPDATE.
Wenn ich jetzt auf Tabelle1 (tAdresse) einen UPDATE Befehl absetze und mehr als eine Zeile betroffen ist,
dann erscheint Meldung 512 mit "Die Unterabfrage hat mehr als einen Wert zurückgegeben.".
Ich dachte, dass INSERTED alle ankommenden Datenzeilen verarbeitet, kann aber wohl doch nur mit einer Zeile umgehen.
Habe nur etwas on "CURSOR" gelesen, aber hatte diese Funktion noch nie genutzt.
Wie müsste ich denn den Trigger umbauen, dass mehrere INSERTED Zeilen verarbeitet werden können?
CREATE TRIGGER [tgr_KUNDENADRESSE] ON [dbo].[tAdresse]
AFTER INSERT, UPDATE
AS
BEGIN
IF (SELECT COUNT(*) FROM [dbo].[KUNDENADRESSE] WHERE TEMP_tAdresse_kAdresse=(SELECT kAdresse FROM inserted)) = '0'
INSERT INTO [dbo].[KUNDENADRESSE] (
TEMP_tAdresse_kAdresse,
TEMP_tAdresse_kKunde,
TEMP_tKunde_cKundenNr,
TEMP_tAdresse_cFirma,
TEMP_tAdresse_cFirma_BACKUP,
TEMP_tAdresse_cZusatz,
TEMP_tAdresse_cZusatz_BACKUP,
TEMP_tAdresse_cTitel,
TEMP_tAdresse_cTitel_BACKUP,
TEMP_tAdresse_cVorname,
TEMP_tAdresse_cVorname_BACKUP,
TEMP_tAdresse_cName,
TEMP_tAdresse_cName_BACKUP,
TEMP_tAdresse_cStrasse,
TEMP_tAdresse_cStrasse_BACKUP,
TEMP_tAdresse_cPLZ,
TEMP_tAdresse_cPLZ_BACKUP,
TEMP_tAdresse_cOrt,
TEMP_tAdresse_cOrt_BACKUP,
TEMP_tAdresse_cAdressZusatz,
TEMP_tAdresse_cAdressZusatz_BACKUP,
TEMP_tAdresse_cUSTID,
TEMP_tAdresse_cUSTID_BACKUP,
TEMP_tkunde_dErstellt,
TEMP_DATE,
TEMP_UNDO,
TEMP_TODO)
(SELECT
inserted.kAdresse,
inserted.kKunde,
'',
'',
inserted.cFirma,
'',
inserted.cZusatz,
'',
inserted.cTitel,
'',
inserted.cVorname,
'',
inserted.cName,
'',
inserted.cStrasse,
'',
inserted.cPLZ,
'',
inserted.cOrt,
'',
inserted.cAdressZusatz,
'',
inserted.cUSTID,
'',
'',
'N',
'N'
FROM inserted);
ELSE
UPDATE [dbo].[KUNDENADRESSE]
SET
TEMP_tKunde_cKundenNr = '',
TEMP_tAdresse_cFirma = cFirma,
TEMP_tAdresse_cZusatz = cZusatz,
TEMP_tAdresse_cTitel = cTitel,
TEMP_tAdresse_cVorname = cVorname,
TEMP_tAdresse_cName = cName,
TEMP_tAdresse_cStrasse = cStrasse,
TEMP_tAdresse_cPLZ = cPLZ,
TEMP_tAdresse_cOrt = cOrt,
TEMP_tAdresse_cAdressZusatz = cAdressZusatz,
TEMP_tAdresse_cUSTID = cUSTID,
TEMP_tkunde_dErstellt = '',
TEMP_DATE = NULL
FROM [dbo].[KUNDENADRESSE] f
INNER JOIN inserted i ON i.kAdresse = f.TEMP_tAdresse_kAdresse;
END
GO
Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben
Content-ID: 395733
Url: https://administrator.de/contentid/395733
Ausgedruckt am: 22.11.2024 um 14:11 Uhr
20 Kommentare
Neuester Kommentar
Moin,
wozu ein Trigger an dieser Stelle, dass kannst Du doch mit T-SQL abfackeln.
Überarbeite Dein Datenbankdesign (Programmablauf), der Trigger ist bei richtigem Design unnötig, und macht, wie Du schon gesehen hast, in anderen Fällen Probleme SQL Trigger für INSERT und UPDATE scheitert, weil INSERTED in Unterabfrage mehr als einen Wert liefert.
Gruss
wozu ein Trigger an dieser Stelle, dass kannst Du doch mit T-SQL abfackeln.
Überarbeite Dein Datenbankdesign (Programmablauf), der Trigger ist bei richtigem Design unnötig, und macht, wie Du schon gesehen hast, in anderen Fällen Probleme SQL Trigger für INSERT und UPDATE scheitert, weil INSERTED in Unterabfrage mehr als einen Wert liefert.
Gruss
Moin chgs2011,
ändere mal folgende Zeile.
IF (SELECT COUNT(*) FROM [dbo].[KUNDENADRESSE] WHERE TEMP_tAdresse_kAdresse=(SELECT kAdresse FROM inserted)) = '0'
in
IF (SELECT COUNT(*) FROM [dbo].[KUNDENADRESSE] WHERE TEMP_tAdresse_kAdresse IN(SELECT kAdresse FROM inserted)) = '0'
Also aus dem = ein IN machen.
Gruß Spaceman127
ändere mal folgende Zeile.
IF (SELECT COUNT(*) FROM [dbo].[KUNDENADRESSE] WHERE TEMP_tAdresse_kAdresse=(SELECT kAdresse FROM inserted)) = '0'
in
IF (SELECT COUNT(*) FROM [dbo].[KUNDENADRESSE] WHERE TEMP_tAdresse_kAdresse IN(SELECT kAdresse FROM inserted)) = '0'
Also aus dem = ein IN machen.
Gruß Spaceman127
Grundsätzlich ist ein Trigger in diesem Szenario eine sinnvolle Wahl. Besser wäre eigentlich nur eine Sicht auf die Daten durch den anderen DB Server. Einen Cursor solltest du unbedingt vermeiden, der ist hier auch gar nicht notwendig.
Problematisch in deinem Trigger ist erstmal diese Zeile:
Wie du schon erkannt hast liefert INSERTED immer alle vom SQL Befehl betroffenen Zeilen, der Trigger feuert genau einmal und du musst alle deine Datensätze mit deinem Code abfrühstücken, so der Plan.
An dieser Stelle könntest du also höchstens
TEMP_tAdresse_kAdresse=(SELECT
durch
TEMP_tAdresse_kAdresse IN (SELECT
ersetzen, denn der Select liefert dir halt auch mehrere Werte zurück und die kannst du mit gleich nicht prüfen.
Aber sinnvoll erscheint mir diese Prüfung allgemein nicht. Du prüfst, ob mind. ein Datensatz aus Inserted schon in der neuen Tabelle vorhanden ist. Wenn das der Fall ist, wird kein Datensatz aus Inserted eingefügt. Also können deine Daten theoretisch inkonsistent sein, auch wenn das vermutlich einfach nicht vorkommt. Nur irgendwie ist das einfach nicht elegant.
Hier mal ein Gegenvorschlag als pseudo Code:
Der erste Block wird immer nur Datensätze einfügen die noch gar nicht existieren. Der zweite Block aktualisiert alle Datensätze die sich in Spalte 1 unterscheiden (Vorsicht beim abgleich mit NULL-Werten und Formaten). Das kann man entweder für alle Spalten einzeln machen oder alles in einem Rutsch. Ich bin immer ein Freund davon nur Daten zu schreiben wenn sie sich dadurch ändern aber das ist eigentlich nur wichtig wenn z.B. ein Logging statt findet.
Alternativ kannst du ein großes MERGE bauen das deine Daten aus Inserted synchronisiert. Beides wird halt durch den Trigger ausgelöst und aktualisiert die Zieltabelle.
Problematisch in deinem Trigger ist erstmal diese Zeile:
IF (SELECT COUNT(*) FROM [dbo].[KUNDENADRESSE] WHERE TEMP_tAdresse_kAdresse=(SELECT kAdresse FROM inserted)) = '0'
An dieser Stelle könntest du also höchstens
TEMP_tAdresse_kAdresse=(SELECT
durch
TEMP_tAdresse_kAdresse IN (SELECT
ersetzen, denn der Select liefert dir halt auch mehrere Werte zurück und die kannst du mit gleich nicht prüfen.
Aber sinnvoll erscheint mir diese Prüfung allgemein nicht. Du prüfst, ob mind. ein Datensatz aus Inserted schon in der neuen Tabelle vorhanden ist. Wenn das der Fall ist, wird kein Datensatz aus Inserted eingefügt. Also können deine Daten theoretisch inkonsistent sein, auch wenn das vermutlich einfach nicht vorkommt. Nur irgendwie ist das einfach nicht elegant.
Hier mal ein Gegenvorschlag als pseudo Code:
INSERT INTO zieltabelle(s1,s2)
SELECT quelltabelle.s1,quelltabelle.s2
FROM inserted quelltabelle
LEFT JOIN zieltabelle
ON quelltabelle.pk = zieltabelle.pk
WHERE zieltabelle.pk IS NULL
UPDATE zieltabelle
SET zieltabelle.s1 = quelltabelle.s1
FROM zieltabelle
INNER JOIN inserted quelltabelle
ON quelltabelle.pk = zieltabelle.pk
WHERE isnull(zieltabelle.s1,'') != isnull(quelltabelle.s1,'')
Alternativ kannst du ein großes MERGE bauen das deine Daten aus Inserted synchronisiert. Beides wird halt durch den Trigger ausgelöst und aktualisiert die Zieltabelle.
Hallo chgs2011,
nachdem Du Dir hier soviel Mühe gegeben hast, soll sie auch belohnt werden:
Den Fehler in Deinem Skript vom Anfang hat Dir Spaceman127 gesagt, das war das "in" statt "=".
In Deiner zurechtgemachten Testumgebung hast Du dann diesen Fehler noch an anderer Stelle eingebaut.
Du mußt darauf achten, wo ein einzelner Wert und wo eine Menge von Werten erwartet wird. Hinter einem "=" wird ein einzelner Wert erwartet. Kommen mehrere Werte an, dann kommt es zu dem Fehler.
Konkret ist das die Zeile 154:
IF (SELECT COUNT(*) FROM [dbo].[KUNDENADRESSE] WHERE TEST_tAdresse_kAdresse=(SELECT kAdresse_TMP FROM inserted)) = '0'
Da hat Dir Spaceman127 schon gesagt, daß es
TEST_tAdresse_kAdresse in (SELECT kAdresse_TMP FROM inserted)
heißen muß.
Außerdem noch viermal der Fehler in den Zeilen 186, 207, 215 und 226:
(SELECT tkunde_TMP.cKundenNr_TMP FROM tkunde_TMP WHERE tkunde_TMP.kKunde_TMP = (SELECT kKunde_TMP FROM inserted))
Da ist hinten derselbe Fehler, wie er schon in Zeile 154 ist und außerdem noch, daß das gesamte select mehrere Zeilen liefert, aber nur ein Wert erwartet wird.
Das löst Du mit:
(SELECT tkunde_TMP.cKundenNr_TMP FROM tkunde_TMP WHERE tkunde_TMP.kKunde_TMP = inserted.kKunde_TMP)
bzw. im update benutzt Du einen Alias, da ist es:
(SELECT tkunde_TMP.cKundenNr_TMP FROM tkunde_TMP WHERE tkunde_TMP.kKunde_TMP = i.kKunde_TMP)
Die Abfrage in Zeile 154 ist übrigens sowieso nicht wirklich gut, die dürfte zu falschen Ergebnissen führen. Angenommen Du aktualierst fünf Adressen und davon ist eine bereits in [KUNDENADRESSE] vorhanden, dann liefert Dein count (*) = 1 und die fehlenden vier Adressen werden nicht angelegt.
Das kann man lösen indem man das if wegläßt und dafür beim insert eine where-Bedingung anhängt:
where not exists (select 1 from [KUNDENADRESSE] where TEST_tAdresse_kAdresse = inserted.kAdresse_TMP)
Oder man verwendet gleich den Befehl merge.
Ich hoffe, ich konnte Deine Verzweiflung abwenden
Gruß, Mad Max
nachdem Du Dir hier soviel Mühe gegeben hast, soll sie auch belohnt werden:
Den Fehler in Deinem Skript vom Anfang hat Dir Spaceman127 gesagt, das war das "in" statt "=".
In Deiner zurechtgemachten Testumgebung hast Du dann diesen Fehler noch an anderer Stelle eingebaut.
Du mußt darauf achten, wo ein einzelner Wert und wo eine Menge von Werten erwartet wird. Hinter einem "=" wird ein einzelner Wert erwartet. Kommen mehrere Werte an, dann kommt es zu dem Fehler.
Konkret ist das die Zeile 154:
IF (SELECT COUNT(*) FROM [dbo].[KUNDENADRESSE] WHERE TEST_tAdresse_kAdresse=(SELECT kAdresse_TMP FROM inserted)) = '0'
Da hat Dir Spaceman127 schon gesagt, daß es
TEST_tAdresse_kAdresse in (SELECT kAdresse_TMP FROM inserted)
heißen muß.
Außerdem noch viermal der Fehler in den Zeilen 186, 207, 215 und 226:
(SELECT tkunde_TMP.cKundenNr_TMP FROM tkunde_TMP WHERE tkunde_TMP.kKunde_TMP = (SELECT kKunde_TMP FROM inserted))
Da ist hinten derselbe Fehler, wie er schon in Zeile 154 ist und außerdem noch, daß das gesamte select mehrere Zeilen liefert, aber nur ein Wert erwartet wird.
Das löst Du mit:
(SELECT tkunde_TMP.cKundenNr_TMP FROM tkunde_TMP WHERE tkunde_TMP.kKunde_TMP = inserted.kKunde_TMP)
bzw. im update benutzt Du einen Alias, da ist es:
(SELECT tkunde_TMP.cKundenNr_TMP FROM tkunde_TMP WHERE tkunde_TMP.kKunde_TMP = i.kKunde_TMP)
Die Abfrage in Zeile 154 ist übrigens sowieso nicht wirklich gut, die dürfte zu falschen Ergebnissen führen. Angenommen Du aktualierst fünf Adressen und davon ist eine bereits in [KUNDENADRESSE] vorhanden, dann liefert Dein count (*) = 1 und die fehlenden vier Adressen werden nicht angelegt.
Das kann man lösen indem man das if wegläßt und dafür beim insert eine where-Bedingung anhängt:
where not exists (select 1 from [KUNDENADRESSE] where TEST_tAdresse_kAdresse = inserted.kAdresse_TMP)
Oder man verwendet gleich den Befehl merge.
Ich hoffe, ich konnte Deine Verzweiflung abwenden
Gruß, Mad Max
Ja hier wurde auch auf einen alten Beitrag von mir verlinkt (im Datenbankforum), mittlerweile habe ich auch dazu gelernt und meine Trigger sehen anders aus. Guck dir mal meinen Pseudo Code an, das kannst du theoretisch 1:1 mit deinen Tabellen umschreiben.
Und / Oder du guckst dir den MERGE-Befehl an, eigentlich muss der Trigger ja nichts anderes machen als bei einer Veränderung der Daten in der Quell-Tabelle einen MERGE zwischen den geänderten Daten (Inahlt von Inserted) und der Zieltabelle durchführen. Das ist mit MERGE nur ein einziges Statement.
PS: Auf das IF kannst du damit eben auch gänzlich verzichten.
Und / Oder du guckst dir den MERGE-Befehl an, eigentlich muss der Trigger ja nichts anderes machen als bei einer Veränderung der Daten in der Quell-Tabelle einen MERGE zwischen den geänderten Daten (Inahlt von Inserted) und der Zieltabelle durchführen. Das ist mit MERGE nur ein einziges Statement.
PS: Auf das IF kannst du damit eben auch gänzlich verzichten.
Ich habe auch noch nicht viel mit MERGE gemacht. Beim letzten Versuch hatte ich irgendein Problem weshalb ich dann davon abgerückt bin und es mit einem INSERT und einem UPDATE wie in meidem Beispiel beschrieben umgesetzt habe.
Hier mein letztes Fall:
Kann dir leider nicht mehr sagen wo es gehakt hat aber die Syntax sollte passen.
Hier mein letztes Fall:
MERGE datev_eodb_mandanten_backup AS t
USING datev_eodb_mandanten AS s
ON t.MDTGUID = s.MDTGUID
AND t.MDTID = s.MDTID
WHEN MATCHED
AND ( isnull(t.MDTVON,''1900-01-01 00:00:00.000'') != isnull(s.MDTVON,''1900-01-01 00:00:00.000'')
OR isnull(t.MDTBIS,''1900-01-01 00:00:00.000'') != isnull(s.MDTBIS,''1900-01-01 00:00:00.000'')
OR isnull(t.MDTSTATUS,0) != isnull(s.MDTSTATUS,0)
OR isnull(t.MDTNR,0) != isnull(s.MDTNR,0)
OR isnull(t.MDTNAME,'''') != isnull(s.MDTNAME,'''')
OR isnull(t.MDTTYP,'''') != isnull(s.MDTTYP,'''')
OR isnull(t.MDTPLZ,'''') != isnull(s.MDTPLZ,'''')
OR isnull(t.MDTORT,'''') != isnull(s.MDTORT,'''')
OR isnull(t.MDTSTR,'''') != isnull(s.MDTSTR,'''')
OR isnull(t.MDTGID,0) != isnull(s.MDTGID,0)
OR isnull(t.MDTGNAME,'''') != isnull(s.MDTGNAME,'''')
OR isnull(t.DOHR,'''') != isnull(s.DOHR,'''') )
THEN
UPDATE
SET t.MDTVON = s.MDTVON,
t.MDTBIS = s.MDTBIS,
t.MDTSTATUS = s.MDTSTATUS,
t.MDTNR = s.MDTNR,
t.MDTNAME = s.MDTNAME,
t.MDTTYP = s.MDTTYP,
t.MDTPLZ = s.MDTPLZ,
t.MDTORT = s.MDTORT,
t.MDTSTR = s.MDTSTR,
t.MDTGID = s.MDTGID,
t.MDTGNAME = s.MDTGNAME,
t.DOHR = s.DOHR
WHEN NOT MATCHED BY TARGET
THEN
INSERT(MDTGUID,MDTID,MDTVON,MDTBIS,MDTSTATUS,MDTNR,MDTNAME,MDTTYP,MDTPLZ,MDTORT,MDTSTR,MDTGID,MDTGNAME,DOHR)
VALUES(s.MDTGUID,s.MDTID,s.MDTVON,s.MDTBIS,s.MDTSTATUS,s.MDTNR,s.MDTNAME,s.MDTTYP,s.MDTPLZ,s.MDTORT,s.MDTSTR,s.MDTGID,s.MDTGNAME,s.DOHR)
WHEN NOT MATCHED BY SOURCE
THEN
DELETE;