Fehler bei DML-Trigger: "Die Unterabfrage hat mehr als einen Wert zurückgegeben....", SQL Server 2005

Question

Hallo,
bin schon deutlich verzweifelt:

Mein DML-Trigger führt zur Fehlermeldung "Die Unterabfrage hat mehr als einen Wert zurückgegeben. Das ist nicht zulässig, wenn...".

Die Triggerdefinition lautet:

"
USE Vorgaenge
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE TRIGGER jhTRU_VorPos ON Vorgaenge
    FOR UPDATE

AS
IF UPDATE(GGErfuellt)
BEGIN
    SET NOCOUNT ON;

    UPDATE Positionen
    SET USER_LastModDatum = GETDATE()
    WHERE VorPosID IN
        (SELECT VorPosID FROM Deleted)

END
GO
"

 

Das soll eigentlich passieren:

In der Tabelle Vorgaenge soll das Feld GGErfuellt auf Veränderung überwacht werden und in diesem Fall in Tabelle Positionen das Feld USER_LastModDatum aktualisiert werden.

Die Tabellen Vorgaenge und Positionen sind über VorPosID miteinander verknüpft.

Ändere ich nun in einem einzelnen Datensatz von Vorgaenge das Feld GGErfuellt erhalte ich die oben beschriebene Fehlermeldung.

Datenbank befindet sich auf einem SQL-Server 2005, meine Versuche habe ich direkt im SQL Server Management Studio (9.00.3042.00) durchgeführt.

 

Hat jemand eine Idee, was da falsch ist oder was blockieren könnte?

 

Bin für jede Hilfe dankbar...
JogiHa

 

---

Mit Grüßen Jörg

Answer 1

Hallo Jörg,

ohne Deine Tabellen zu kennen ist das ein Ratespiel.
Grundsätzlich macht die Anweisung nichts verkehrt.
Ein WHERE X = IN (SELECT ...) darf hier mehrere Zeilen liefern
und sollte nicht nicht die Fehlerursache sein.

Allerdings ist der Trigger in vielerlei Hinsicht zu "optimistisch",
was Änderungen angeht.
IF UPDATE(...) prüft nicht, ob der Wert verändert wurde,
sondern besagt nur, dass die Spalte in der Anweisung angegeben wurde.

Eine bessere Variante wäre:

CREATE TRIGGER jhTRU_VorPos 
    ON Vorgaenge
    FOR UPDATE
AS
    SET NOCOUNT ON;
    IF UPDATE(GGErfuellt)
    BEGIN
        UPDATE Positionen
        SET USER_LastModDatum = GETDATE()
        FROM Positionen 
        INNER JOIN inserted AS i ON Positionen.VorPosID = i.VorPosID
        INNER JOIN deleted AS d ON Positionen.VorPosID = d.VorPosID
        WHERE i.GGErfuellt <> d.GGErfuellt;
    END

Zur Verdeutlichung mal eine für aus Deiner Beschreibung abgeleitete Tabellen:

-- Für Test in tempdb
USE tempdb;
SET NOCOUNT ON;
GO

CREATE TABLE dbo.Vorgaenge
(
    VorPosID int NOT NULL PRIMARY KEY,
    GGErfuellt int NOT NULL DEFAULT(0),
    Daten nvarchar(40) NULL
);

CREATE TABLE dbo.Positionen
(
    PositionenID int NOT NULL PRIMARY KEY,
    VorPosID int NOT NULL,
    USER_LastModDatum datetime NOT NULL DEFAULT(GETDATE())
);
GO


INSERT INTO Vorgaenge VALUES (1000, 0, DEFAULT);
INSERT INTO Positionen VALUES (1001, 1000, '19000101');

INSERT INTO Vorgaenge VALUES (2000, 0, DEFAULT);
INSERT INTO Positionen VALUES (2001, 2000, '19000101');
INSERT INTO Positionen VALUES (2002, 2000, '19000101');

INSERT INTO Vorgaenge VALUES (3000, 0, DEFAULT);

INSERT INTO Vorgaenge VALUES (3100, 0, DEFAULT);
INSERT INTO Positionen VALUES (3101, 3100, '19000101');
INSERT INTO Positionen VALUES (3102, 3100, '19000101');

INSERT INTO Vorgaenge VALUES (3200, 1, DEFAULT);
INSERT INTO Positionen VALUES (3201, 3200, '19000101');
INSERT INTO Positionen VALUES (3202, 3200, '19000101');
GO

INSERT INTO Vorgaenge VALUES (3300, 0, DEFAULT);
INSERT INTO Positionen VALUES (3301, 3300, '19000101');
INSERT INTO Positionen VALUES (3302, 3300, '19000101');
GO

/*
 * Einige Varianten von Aktualisierungen
 */

-- Einzeiler
UPDATE dbo.Vorgaenge SET GGErfuellt = 1 WHERE VorPosID = 1000;
-- Mehrere Zeilen
UPDATE dbo.Vorgaenge SET GGErfuellt = GGErfuellt WHERE VorPosID = 2000;
-- keine Zeilen
UPDATE dbo.Vorgaenge SET GGErfuellt = GGErfuellt WHERE VorPosID = 3000;

-- Bedingte Änderung
UPDATE dbo.Vorgaenge 
SET Daten = 'ABC', 
    GGErfuellt = CASE 
        WHEN VorPosID BETWEEN 3100 AND 3200 
        THEN 1 ELSE 0 END
WHERE VorPosID BETWEEN 3000 AND 3200;

SELECT * FROM Positionen WHERE USER_LastModDatum <> '19000101';
GO

-- Aufräumen
DROP TABLE Positionen, Vorgaenge
GO

Wenn Du Deinen Trigger mit dem geänderten vergleichst,
solltest Du einige Unterschiede bemerken ;-)

Was den Fehler angeht: Bleibt es auch bei meiner Trigger Variante
bei dem Fehler müsstest Du die Tabellenbeschreibungen posten
(bitte aber nicht wieder so fett; das tut den Augen weh ;-)

Gruß Elmar

Answer 2

Hallo Elmar,

Deine Trigger-Lösung gefällt mir natürlich wesentlich besser als meine bisherige (ich bin auch nicht wirklich ein SQL-Crack...) - habe sie nun so umgesetzt.

Der Fehler kam allerdings trotzdem wieder (sonst hätte ich wirklich an mir zweifeln müssen ;-)).

 

Die Datenbank ist nicht wirklich die meine, d.h. ich arbeite in einem bereits existierenden Datenbanksystem, um zusätzliche Funktionalitäten einzubringen.

Letztendlich habe ich nun rausgefunden, dass ein DML-Trigger auf der Tabelle Positionen aktiv war, der den eigentlichen Fehler dann verursacht hat.

 

Nochmals vielen Dank für Deine Unterstützung, habe wieder ein großes Stück dazugelernt.

 

Grüße Jörg

 

---

Mit Grüßen Jörg

Answer 3

Hallo Jörg,

schön das Du den Fehler gefunden hast.
Wenn ein Trigger auf der Tabelle Positionen der Verursacher war,
solltest Du ihn Dir genauer anschauen, denn der Fehlermeldung nach,
dürfte er nicht mit mehreren Zeilen umgehen können.

Das von mir gezeigte Muster lässt sich für Aktualisierungen relativ
verallgemeinert anwenden.

Gruß Elmar