Trigger - Insert in andere Tabelle

[speedone]

Hallo Leute,

ich möchte bei einem Insert oder Update auf Tabelle A einen Insert in Tabelle B machen, sofern in Tabelle A ein logisches Feld auf true gesetzt ist und anschließend eine Referenz schaffen.

Also z. B Tabelle A:
ID, TabelleBID, ErstelleBEintrag (bit)

INSERT oder UPDATE INTO TabelleA (ID, ErstelleBEintrag) VALUES (1, 1)

führt einen leeren Insert auf TabelleB aus und trägt dessen erstellte ID in Tabelle A ein und setzt ErstelleBEintrag auf 0 bzw. false:

Tabelle A danach:
ID, TabelleBID, ErstelleBEintrag (bit)
1, 1000001, 0

Tabelle B:
ID, weitere Felder leer
1000001, ..............


Ich habe das ganze bereits mit einer SQL WHILE Schleife im Trigger umgesetzt, die allerdings nicht sehr performant ist:

SELECT * INTO #tmpinserted FROM inserted
DECLARE @id uniqueidentifier

WHILE (SELECT Count(*) FROM #tmpinserted) > 0
BEGIN
  SELECT TOP 1 @id= id FROM #tmpinserted

  /* Pseudocode */
  wenn ErstelleBId == 1
  dann insert in Tabelle B und anschließendes Update auf Tabelle A mit SCOPE_IDENTITY von TabelleB
  /* Pseudocode ende */

  DELETE #tmpinserted WHERE id= @id
END

Lässt sich das ganze auch effizienter umsetzen ohne auf die Sinnhaftigkeit des Vorgangs einzugehen.
Gerade beim Update auf TabelleA sind auch schnell mal ein paar mehr Datensätze betroffen bei der die WHILE Schleife recht langsam ist. Weiterhin werden noch mehr Vorgänge ausgeführt, auf die ich hier nicht weiter eingehen möchte (Sync bestimmter Felder zwischen den Tabellen 1 zu 1 Beziehung)

 

[akretschmer]

Hallo Leute,

ich möchte bei einem Insert oder Update auf Tabelle A einen Insert in Tabelle B machen, sofern in Tabelle A ein logisches Feld auf true gesetzt ist und anschließend eine Referenz schaffen.

Also z. B Tabelle A:
ID, TabelleBID, ErstelleBEintrag (bit)

INSERT oder UPDATE INTO TabelleA (ID, ErstelleBEintrag) VALUES (1, 1)

führt einen leeren Insert auf TabelleB aus und trägt dessen erstellte ID in Tabelle A ein und setzt ErstelleBEintrag auf 0 bzw. false:

Tabelle A danach:
ID, TabelleBID, ErstelleBEintrag (bit)
1, 1000001, 0

Nein. Du trägst ja (1,1) ein, damit ist dann (1,NULL,1) eingetragen, TabelleA. TabelleB ist davon gar nicht betroffen.
Wenn es darum geht, eine generierte ID (Serial bei PostgreSQL z.B.) dann woanders einzutragen so haben eigentlich alle Datenbanken Funktionen, diese zu erfragen. Noch eleganter kann man mit WITH-Syntax und wCTE (writeable Common Table Extensions) arbeiten, aber ich weiß nicht, ob M$SQL das kann.

 

[speedone]

genau die ID wird in Tabelle B generiert. Diese generierte ID soll dann in Tabelle A referenziert werden:
So stelle ich mir das in etwas vor, was aber so nicht klappt:
Die Bedingung ob ErstelleBID = 1 ist muss ja auch noch irgendwie für jeden geinserten oder geupdateten Datensatz überprüft werden

BEGIN
UPDATE Tabelle A SET ErstelleBID = 0, TabelleBID = (INSERT INTO TabelleA (irgendeinfeld) VALUES ('leer') OUTPUT id)
END

Wie gesagt mit meiner WHILE Schleife funktioniert das bereits. Ich suche nur nach einer besseren und performanteren Lösung.

Habe kurz mal geschaut, ich glaube MSSQL hat diese WITH-Syntax. Ich nutze einen MS SQL 2012 Server

 

[akretschmer]

genau die ID wird in Tabelle B generiert. Diese generierte ID soll dann in Tabelle A referenziert werden:
So stelle ich mir das in etwas vor, was aber so nicht klappt:
Die Bedingung ob ErstelleBID = 1 ist muss ja auch noch irgendwie für jeden geinserten oder geupdateten Datensatz überprüft werden

BEGIN
UPDATE Tabelle A SET ErstelleBID = 0, TabelleBID = (INSERT INTO TabelleA (irgendeinfeld) VALUES ('leer') OUTPUT id)
END

Dann trage doch in TabelleB ein, erfrage die ID, und trage das dann in TabelleA ein. In PG ginge das in einem Statement, in M$SQL wirst das wohl so tippel-tappel-tour machen müssen.

Aber möglicherweise versteh ich auch Dein Gesamtproblem nicht, Dein ErstelleBID macht mich grad a bissl irre...

 

[speedone]

Das bekomme ich gerade nicht hin, wegen der Bedingung, dass ErstelleBID = 1 sein muss, damit das ganze überhaupt gemacht werden soll.
Das Feld ErstelleBID fungiert als eine Art Schalter. Wenn er beim Insert oder Update auf 1 beim jeweiligen Datensatz gesetzt ist, soll überhaupt der INSERT auf die TabelleB erfolgen. Danach wird der Schalter wieder auf 0 resettet, damit beim nächsten mal nicht wieder ein neuer Datensatz in B angelegt wird.

Beim Update Trigger können ja mehrere Datensätze betroffen sein.
Es muss also für jeden geänderten Datensatz überprüft werden, ob ErstelleBID = 1 ist und nur sofern das zutrifft, soll der INSERT auf TabelleB gemacht werden und die ID zurückgeschrieben werden.

Möglicherweise lässt sich das wirklich nur mit einer Schleife lösen, wie ich es bereits umgesetzt habe.

 

[speedone]

Beziehungsweise mir fällt gerade ein, dass bei einem UPDATE Statement eigentlich alle geänderten Datensätze entweder ErstelleBID = 1 oder 0 haben müssten.
Damit kann man die Bedingung vereinfachen:

IF EXISTS (SELECT id FROM inserted WHERE ErstelleBID =1)
BEGIN
UPDATE Tabelle A SET ErstelleBID = 0, TabelleBID = (INSERT INTO TabelleB (irgendeinfeld) VALUES ('leer') OUTPUT id)
END

Wie müsste das hier richtig angewiesen (Syntax) werden, dass das überhaupt funktioniert und für jeden geänderten Datensatz passiert?

 

[ukulele]

Also auf jedenfall kann man da so einiges verschlanken, jetzt mal ungetestet:

INSERT INTO TabelleB(pk_b,fk_a,spalte)
SELECT    newid() AS pk_b,
        pk_a AS fk_a,
        spalte
FROM    INSERTED
WHERE    ErstelleBId = 1

UPDATE    TabelleA
SET        fk_b = pk_b
FROM    TabelleA
INNER JOIN TabelleB
ON        TabelleA.pk_a = TabelleB.fk_a
WHERE    fk_b != pk_b

Entscheidend ist hier das du den pk von TabelleA mit in TabelleB schreibst und dann darauf in deinem UPDATE Joinst.

 

[akretschmer]

Beim Update Trigger können ja mehrere Datensätze betroffen sein.

Ja.

Es muss also für jeden geänderten Datensatz überprüft werden, ob ErstelleBID = 1 ist und nur sofern das zutrifft, soll der INSERT auf TabelleB gemacht werden und die ID zurückgeschrieben werden.

Möglicherweise lässt sich das wirklich nur mit einer Schleife lösen, wie ich es bereits umgesetzt habe.

Nein. Ein TRIGGER kann per Statement ODER per ROW feuern. Du suchst letzteres.

 

[ukulele]

MSSQL Trigger feuern immer nur per Statement. Innerhalb des Triggers liefert die INSERTED Tabelle dann aber alle betroffenen Datensätze zurück.

 

[akretschmer]

MSSQL Trigger feuern immer nur per Statement. Innerhalb des Triggers liefert die INSERTED Tabelle dann aber alle betroffenen Datensätze zurück.

Ah okay.

 

[speedone]

Ich werde mir das mal anschauen. Danke schonmal

 

[speedone]

Also auf jedenfall kann man da so einiges verschlanken, jetzt mal ungetestet:

INSERT INTO TabelleB(pk_b,fk_a,spalte)
SELECT    newid() AS pk_b,
        pk_a AS fk_a,
        spalte
FROM    INSERTED
WHERE    ErstelleBId = 1

UPDATE    TabelleA
SET        fk_b = pk_b
FROM    TabelleA
INNER JOIN TabelleB
ON        TabelleA.pk_a = TabelleB.fk_a
WHERE    fk_b != pk_b

Entscheidend ist hier das du den pk von TabelleA mit in TabelleB schreibst und dann darauf in deinem UPDATE Joinst.

Leider kann ich diese Lösung nicht verwenden, da die Tabellenbeziehung zwischen A und B nun doch nicht 1 zu 1 sein wird, sondern wegen weiterer Funktionalität mit Duplikaten TabelleA "n zu 1" TabelleB.
Lässt sich das auch irgendwie anders verstricken?

 

[ukulele]

Sicher dein Select muss halt die passenden Daten für das Update liefern und zwar immer genau einen Wert pro PK für die zu aktualisierende Tabelle. Mehr als ein Wert pro Zeile läßt sich logischerweise nicht in die Spalte schreiben.