Trigger Problem, Nur Update wenn zwei Werte unterschiedlich sind

Question

Hallo zusammen,

ich bin nicht so der Trigger Fachmann, daher hier die Frage.

Folgenden Trigger habe ich hinbekommen.

ALTER TRIGGER [dbo].[updatePaymentHead] ON [MYDB].[dbo].[Rechnungen]
AFTER UPDATE
AS
If UPDATE(OPEN_NET_AMOUNT) 
BEGIN
   SET NOCOUNT ON;
   
   DECLARE @myID AS INT
   SELECT @myID = [id] FROM INSERTED

   UPDATE [MYDB].[dbo].[Rechnungen] SET
   [LAST_PAYMENT_AT] = GETDATE(),
   [STATUS] = 
	CASE 
		WHEN OPEN_NET_AMOUNT = 0  THEN 'PAID'
		WHEN OPEN_NET_AMOUNT > 0 AND OPEN_NET_AMOUNT < TOTAL_NET_AMOUNT THEN 'PARTPAYMENT'
		WHEN OPEN_NET_AMOUNT < 0  THEN 'REPAYMENT'
		WHEN OPEN_NET_AMOUNT = TOTAL_NET_AMOUNT THEN 'OPEN'
		WHEN OPEN_NET_AMOUNT > TOTAL_NET_AMOUNT THEN 'OPEN'
	END
   WHERE [MYDB].[dbo].[Rechnungen].[id] = @myID

END

Wenn jemand die Spalte OPEN_NET_AMOUNT ändert, soll der Trigger auslösen und ein Datum bei LAST_PAYMENT_AT eintragen und die Spalte STATUS updaten.

Das Problem dabei ist.

Es handelt sich um eine spezielle Wenanwendung auf die ich keinen Einfluss habe. Bisher konnte man als Anwender nur die Spalte "OPEN_NET_AMOUNT" editieren. Jetzt gibt es aber noch eine Weitere Spalte "INFORMATION" als Textfeld. Und die Anwendung macht leider ein Update über alle Spalten. Sprich , auch wenn man nur in INFORMATION etwas eingibt, wird ein Update auf OPEN_NET_AMOUNT gemacht und somit der Trigger ausgelöst und es steht ein Dateum bei LAST_PAYMENT_AT drin. Das sollte aber nur drin stehen, wenn der OPEN_NET_AMOUNT geändert wurde.

Jetzt suche ich nach einer Möglichkeit dies zu verhindern.

Ich habe mir überlegt, dass man zusätzlich einen Vergleich von OPEN_NET_AMOUNT und TOTAL_NET_AMOUNT macht.

also etwa so:

If UPDATE(OPEN_NET_AMOUNT) AND OPEN_NET_AMOUNT != TOTAL_NET_AMOUNT

aber ich vermute so einfach geht es nicht?

Muss man die beiden Spalten vorher holen oder deklarieren?

Ich hoffe ich habe mich klar ausgedrückt.

vielen Dank

Gruss

Hans

Answer 1

Verhindern kann man da leider überhaupt nichts.

 Der After Update-Trigger löst immer aus, sobald es einen oder aber auch mehrere Updates gibt.
Du behandelst in deinem Trigger auch nur den 1. Update überhaupt.

Im SQL-Server wird der After-Trigger erst aufgerufen, wenn alle Zeilen eiens Updates erledigt sind. Es können also auch mehrere Zeilen mit einem Update betroffen sein.

Näheres dazu findest du hier:
https://docs.microsoft.com/de-de/sql/relational-databases/triggers/use-the-inserted-and-deleted-tables?view=sql-server-2017

Das Problem des Updates z.B. ist, dass dieser per Delete/Insert intern geregelt wird.
In der "Deleted" Tabelle hast du den vorherigen Datensatz, in der "Inserted"-Tabelle den aktuellen Satz.

Um nun den Abgleich Alt <-> Neu festzustellen, musst du die Inserted-Tabelle durchlesen und mit der Deleted-Tabelle abgleichen.
I.d.R. ist ggf. nur 1 Satz in der Tabelle.
Wenn du dann allerdings selber einen Update auf die Tabelle durchführst musst du bedenken, dass dies dann wiederum in einer "Versionstabelle" zwischengespeichert wird und erst nach Transaktionsende für andere Transaktionen sichtbar wird. Solange deine Transaktion nicht abgeschlossen ist, sehen andere Transaktionen nur die vorherigen Daten, also im Zweifel die gerade geänderten Zeilen vor Aufruf des Triggers, falls diese nicht auch als Datenversion gerade bearbeitet werden.

Answer 2

Hallo,

danke dir,

und vorher die Werte holen ala;

DECLARE @opennet = Select Open_net_amount from mydb.dbo.Rechnungen

DECLARE @totalnet = Select Total_net_amount from mydb.dbo.Rechnungen

und dann ein;

If UPDATE(OPEN_NET_AMOUNT) AND opennet != totalnet

geht vermutlich auch nicht?

Answer 3

Stimmt, da du einen After-Trigger hast (Before gibts ja leider nicht), holst du aus der Tabelle rechnungen nur die aktuellen Werte.
Wie gesagt, die vorherigen Werte stehen in der Tabelle "deleted".

Die Frage "if UPDATE(Feld)" besagt leider nichts darüber ob sich tatsächlich eine Änderung ergeben hat, sondern nur, ob in dem Update-Statement die Spalte "Feld" verwendet wurde.

Desweiteren definiert dein Declare einen Cursor und nicht einen einzelnen Satz.
Schau dir diesen Link nochmal an:
http://sqlhints.com/2016/02/28/inserted-and-deleted-logical-tables-in-sql-server/

Mittels Cursor "select f1, f2, ..., fn from inserted" liest du die geänderten Sätze, wobei eben f1...fn die von dir benötigten felder sind.
Mittels "Select f1, f2, ... fx from deleted where deleted.fx = fx" bekommst du den vorherigen inhalt und kannst dann deinen Abgleich und ggf. folgenden Update machen, wobei hier mit fx dein eindeutiger Schlüssel gemeint ist.

Bedenke aber dabei, dass dein Trigger anschließend wieder aufgerufen wird, da du ja einen Update machst.
Stelle also sicher, das du nicht anfängst zu kreisen.

Answer 4

Hallo,

ich muss nochmals nachfragen, da ich nicht sicher bin von der Schreibweise und Position

Ausgehend von oben gezeigten Trigger.

Kann man diese Declare mit Deleted und Inserted so schreiben und auch vor dem BEGIN setzen?

Weil ich brauche ja den Vergleich des Betrages schon bei

IF UPDATE(OPEN_NET_AMOUNT) AND @openNetDeleted != @openNetInserted

ALTER TRIGGER [dbo].[updatePaymentHead] ON [MYDB].[dbo].[Rechnungen]
AFTER UPDATE
AS
DECLARE @openNetDeleted AS decimal(18,2); 
SET @openNetDeleted = (SELECT OPEN_NET_AMOUNT FROM DELETED);

DECLARE @openNetInserted AS decimal(18,2); 
SET @openNetInserted = (SELECT OPEN_NET_AMOUNT FROM INSERTED);

-- VORHER If UPDATE(OPEN_NET_AMOUNT) 
-- NEU
IF UPDATE(OPEN_NET_AMOUNT) AND @openNetDeleted != @openNetInserted

BEGIN
   SET NOCOUNT ON;
   
   DECLARE @myID AS INT
   SELECT @myID = [id] FROM INSERTED

Answer 5

SET @openNetDeleted = (SELECT OPEN_NET_AMOUNT FROM DELETED);

Dies klappt solange, bis mehr als 1 Zeile mit einem Update verändert wurden.
Ist dies nämlich der Fall bekommst du bei diesem Select einen Abbruch der Prozedur und der Transaktion.
Deshalb würde ich dies so nicht machen sondern wirklich mit Cursor auf "insert" und "select .. from deleted where ..." arbeiten.

Oder du fragst per "select count(*) from inserted" ab, wieviele Zeilen geändert wurden um bei mehr als 1 eine Exception auszuwerfen bzw. einen Rollback der Transaktion gezielt auszuführen.
Dies kann aber massive Probleme auf deine Anwendung bringen, denn es kann ja durchaus begründet Updates von mehreren Zeilen in einem SQL geben.

Answer 6

Hallo hawk-master,

du kannst das Ganze innerhalb der UPDATE Abfrage abwickeln, in dem Du eine WHERE Bedingung verwendest, die nur die ID's liefert, die bei denen sich der Betrag geändert hat:

UPDATE [MYDB].[dbo].[Rechnungen] SET
   [LAST_PAYMENT_AT] = GETDATE(),
   [STATUS] = 
	CASE 
		WHEN OPEN_NET_AMOUNT = 0  THEN 'PAID'
		WHEN OPEN_NET_AMOUNT > 0 AND OPEN_NET_AMOUNT < TOTAL_NET_AMOUNT THEN 'PARTPAYMENT'
		WHEN OPEN_NET_AMOUNT < 0  THEN 'REPAYMENT'
		WHEN OPEN_NET_AMOUNT = TOTAL_NET_AMOUNT THEN 'OPEN'
		WHEN OPEN_NET_AMOUNT > TOTAL_NET_AMOUNT THEN 'OPEN'
	END
   WHERE [MYDB].[dbo].[Rechnungen].[id] 
	IN (SELECT i.id FROM inserted AS id	
		INNER JOIN deleted AS d ON i.id = d.id
		WHERE i.OPEN_NET_AMOUNT <> d.OPEN_NET_AMOUNT)

Falls notwendig die WHERE Klausel um weitere Bedingungen erweitern. Prüfe bitte auch ob die Spalten NULL Werte annehmen können, dann wären zusätzliche Bedingungen erforderlich.

Auf das Abrufen von Werten, was nur bei einzeiligen Updates funktioniert, solltest du verzichten. Auch IF UPDATE(...) kannst du u. U. verzichten, denn das besagt nur, dass die Spalte innerhalb der Anweisung angesprochen wurde, nicht ob es sich effektiv der Inhalt verändert hat. Und die auslösende Anwendung scheint es dabei eher locker zu sehen ;)

Gruß Elmar

Answer 7

Hallo Elmar,

danke dir für die Hilfe.

Das setzt aber vorraus, dass meine Tabelle "Rechnungen" eine eindeutige ID (auto increment) hat oder?

Gruss

Hans

Answer 8

Hallo Hans,

das allerdings.

Aber wenn ich mir deinen Code so ansehe, geht der ohnehin davon aus. Ich habe nur das SQL deinen Absichten angepasst, wie ich sie dem ersten Beitrag entnommen habe.

Gruß Elmar

Answer 9

"Und die auslösende Anwendung scheint es dabei eher locker zu sehen ;)"

Dies liegt eher daran, dass diverse Frameworks eben SQL's automatisch generieren und eben alle Felder verwenden, die in einem Resultset/Dataset vorhanden sind. Wer macht sich heute noch wirklich die Mühe, jeden einzelnen SQL zu optimieren wenn das Framework dies ja für mich erledigen sollte (siehe .Net DataAdapter)?

Und wie ich oben schon erwähnte:
Sicherstellen, dass durch diesen Update nicht der Trigger nochmal aufgerufen wird, ggf. durch Ergänzen der Where-Klausel mit

and [LAST_PAYMENT_AT] <> GETDATE()

Ein ID-Feld ist nicht nötig, wenn du u.U. mehrere Felder als eindeutige Identifikation des Datansatzes hast (BelegNr + Datum, oder Beleg-Nr. + Laufende Nr.).
Ggf. mal die Tabelle auf Primary-Key und Indizes der Tabelle auf Unique prüfen.

Answer 10

Hallo Elmar,

sorry aber auf was bezieht sich dein "i" bei

SELECT i.id FROM inserted AS id	
		INNER JOIN deleted AS d ON i.id

Wenn ich das so in SSMS eingebe dann wird mir alles mit "i" rot unterstrichen.

Müsste das event. so heissen?

 WHERE [MYDB].[dbo].[Rechnungen].[id] AS i

aber vermutlich meintest du:

SELECT i.id FROM inserted AS i    und nicht id ?

Answer 11

Fast richtig, gemeint ist wohl eher

IN (SELECT i.id FROM inserted AS i