Trigger liefert mehr als einen Eintrag aus INSERTED Tabelle

[ukulele]

Ich habe mal ein Test-Szenario aufgesetzt, Tabelle tmp und Inhalt:
pk var bit
------------------------------------ -------------------------------------------------- -----
7915F803-C54D-4552-9146-701559FA66AA Test 0
5951FFBA-22F4-46B5-B861-B786A8A4BE1C Test 0
9EF696E6-E8DD-40FB-A241-1CE6E97A16F9 Test 0
58F0A37A-526E-46EF-A8E4-AB6666D4558B Test 0
59361A39-B0BD-4930-B801-BB7FFEE10EA9 Test 1
98F2745B-EB8D-44E1-B4F7-21ECB4894FF9 Test 1
694117F2-5FA8-4E83-AC17-94B17D5C1C5A Test 1
0255AB67-47F1-453B-8629-3C731FBD6D96 Test 1
5F7869AF-F26A-4323-BF30-203CF0A09553 Test NULL
600F7B40-C033-4AB7-B81A-26418F3F0E9E Test NULL
31243BD7-A6E8-4049-B942-1D3422650A21 Test NULL
932EF3E7-AFA2-4C03-B603-6F733BD3CD69 Test NULL

(12 Zeile(n) betroffen)

Auf der Tabelle läuft ein Trigger:

ALTER TRIGGER    [dbo].[tmp_test]
    ON            [dbo].[tmp]
    AFTER UPDATE, INSERT
AS
IF        UPDATE([bit])
AND    (    SELECT    [var]
        FROM    INSERTED ) NOT IN ( 'SQL Trigger','SQL Script' )
BEGIN
    SET NOCOUNT ON;
 
    SELECT    *
    FROM    INSERTED
END
GO

Nun versuche ich folgenden Code auszuführen:

UPDATE    tmp
SET        [bit] = 1,
        [var] = 'SQL Trigger'
WHERE    [bit] = 0
OR        [bit] IS NULL

Meldung 512, Ebene 16, Status 1, Prozedur tmp_test, Zeile 8
Die Unterabfrage hat mehr als einen Wert zurückgegeben. Das ist nicht zulässig, wenn die Unterabfrage auf =, !=, <, <=, > oder >= folgt oder als Ausdruck verwendet wird.
Die Anweisung wurde beendet.

Ganz offensichtlich liefert SELECT [var] FROM INSERTED mehr als eine Zeile zurück. Bisher nahm ich immer an, werden mit einem SQL Statement in einer Tabelle mehrere Zeilen aktualisiert dann feuert der Trigger nur in der ersten Zeile. So wie es aussieht feuert er zwar nur einmal aber in der INSERTED Tabelle stehen dann alle aktualisierten Einträge. Lag ich bisher einfach nur falsch? Kennt jemand eine gute Quelle zum nachlesen?

 

[ukulele]

PS: [var] ist eigentlich mein lastuser auf dem Datensatz und da ich zwei Tabellen habe die sich gegenseitig aktualisieren möchte ich Rekursion vermeiden. Irgendeine tolle andere Idee wie ich das anstelle?

 

[Tommi]

Hallo ukulele,

ich bin ja nicht wirklich ein Freund von Triggern ( ... hatte ich das schon mal erwähnt? ), aber manchmal kommt man da ja leider nicht drum rum.

Für deine Fragestellung sehe ich zwei Lösungen.

1. Da sich die Tabellen ja anscheinend inhaltlich gegenseitig beeinflussen, könnte man natürlich zusätzliche Tabellen erstellen, in der die jeweils abhängigen Daten erfasst werden.
Da du diese Lösung nicht selbst wählst gehe ich davon aus, das dies nicht möglich oder zu aufwändig ist.

dann hättest du

2. noch die Möglichkeit innerhalb des Triggers mit einem CURSOR zu arbeiten.

Für dein Beispiel könnte das dann so aussehen:

ALTER TRIGGER    [dbo].[tmp_test]
    ON            [dbo].[tmp]
    FOR UPDATE
AS
IF        UPDATE([bit])
AND    (    SELECT    [var]
        FROM    INSERTED ) NOT IN ( 'SQL Trigger','SQL Script' )
BEGIN
    SET NOCOUNT ON;
 
    DECLARE @MAX int
    SELECT @MAX=Count(*) FROM INSERTED
 
    IF @MAX>1 AND UPDATE(bit)
    BEGIN
        DECLARE @PK uniqueidentifier, @var varchar(255), @B bit
 
        DECLARE cur CURSOR FOR
        SELECT pk, var, bit
 
        OPEN cur
 
        FETCH NEXT FROM cur INTO @PK, @var, @B
 
        WHILE @@FETCH_STATUS=0
        BEGIN
            IF @var NOT IN ( 'SQL Trigger','SQL Script' ) UPDATE [dbo].[tmp_test]  SET bit = @B WHERE PK = @PK
 
            FETCH NEXT FROM cur INTO @PK, @var, @B
        END
 
        CLOSE cur
        DEALLOCATE cur
 
        RETURN
 
    END
END
GO

Viele Grüße,
Tommi

 

[ukulele]

Hi Tommi,

ja im Grunde stimme ich dir zu, die Datenhaltung ist nicht optimal aber notwendig. Das neue CRM das ich baue kann Kontakte zu Gruppen zusammen fassen. Das geht auf der Seite des grade geöffneten Kontakts (Maske aus dem CRM Tool) für die wichtigsten Gruppen als Haken, leider muss das eine BIT Spalte in der selben Tabelle sein. Wird das BIT gesetzt, schreibt der Trigger die Gruppenzugehörigkeit in die Zwischentabelle. Natürlich kann auch anders eine Gruppenzuordnung entstehen also muss ein anderer Trigger auch wieder das BIT setzen sonst ist es wiedersprüchlich.

Da es noch mehr Trigger gibt die sich gegenseitig beeinflussen habe ich lange gegraben und geflucht. Ich verstehe immer noch nicht so ganz wieso ein SELECT lastuser FROM INSERTED mehr als einen Treffer liefert, er später, innerhalb des gestarteten Triggers, wenn er die Variablen des Triggers mit FROM INSERTED füllt aber nicht mehr mekert. Ich kann das nicht nachvollziehen.

Ich derzeit folgende Zeile, die "funktioniert":

IF    (    UPDATE(gru_edv)
OR        UPDATE(gru_privat)
OR        UPDATE(gru_mitarbeiter)
OR        UPDATE(gru_bewerber) )
AND    (    SELECT    TOP 1 lastuser
        FROM    INSERTED ) NOT IN ( 'SQL Trigger','SQL Script' )
BEGIN

Funktionieren tut das ganze weil einzelne User immer nur einen Datensatz gleichzeit ändern und der Trigger dann auch auslöst, wie er sollte. Wenn mehrere Datensätze aktualisiert werden passiert das immer als User SQL Trigger oder Script. Alle Datensätze haben dann diesen Wert also reicht ein TOP 1 lastuser aber schön finde ich das nicht.

Auch habe ich folgendes probiert und es kommt zum selben Fehler, obwohl der Trigger aus meiner Sicht nicht auslösen dürfte als User SQL Trigger:

IF    (    UPDATE(gru_edv)
OR        UPDATE(gru_privat)
OR        UPDATE(gru_mitarbeiter)
OR        UPDATE(gru_bewerber) )
AND NOT EXISTS (    SELECT    1
                    FROM    INSERTED
                    WHERE    lastuser IN ( 'SQL Trigger','SQL Script' ) )
BEGIN