Changes between Version 1 and Version 2 of Trigger2

Timestamp:

06/28/26 15:34:47 (3 hours ago)

Author:

211101

Comment:

--

Trigger2

@@ -30 +30 @@
     IF TG_OP = 'DELETE' THEN
         UPDATE transaction_account
-        SET balance = balance - OLD.earned_amount + OLD.spent_amount
+        SET balance = balance
+            - COALESCE(OLD.earned_amount, 0)
+            + COALESCE(OLD.spent_amount, 0)
         WHERE transaction_account_id = OLD.transaction_account_id;
 
@@ -36 +38 @@
     ELSIF TG_OP = 'INSERT' THEN
         UPDATE transaction_account
-        SET balance = balance + NEW.earned_amount - NEW.spent_amount
+        SET balance = balance
+            + COALESCE(NEW.earned_amount, 0)
+            - COALESCE(NEW.spent_amount, 0)
         WHERE transaction_account_id = NEW.transaction_account_id;
 
     -- При ажурирање на постоечки запис
     ELSIF TG_OP = 'UPDATE' THEN
-        UPDATE transaction_account
-        SET balance = balance
-            - OLD.earned_amount + OLD.spent_amount
-            + NEW.earned_amount - NEW.spent_amount
-        WHERE transaction_account_id = NEW.transaction_account_id;
+        IF OLD.transaction_account_id = NEW.transaction_account_id THEN
+            UPDATE transaction_account
+            SET balance = balance
+                - COALESCE(OLD.earned_amount, 0)
+                + COALESCE(OLD.spent_amount, 0)
+                + COALESCE(NEW.earned_amount, 0)
+                - COALESCE(NEW.spent_amount, 0)
+            WHERE transaction_account_id = NEW.transaction_account_id;
+        ELSE
+            UPDATE transaction_account
+            SET balance = balance
+                - COALESCE(OLD.earned_amount, 0)
+                + COALESCE(OLD.spent_amount, 0)
+            WHERE transaction_account_id = OLD.transaction_account_id;
+
+            UPDATE transaction_account
+            SET balance = balance
+                + COALESCE(NEW.earned_amount, 0)
+                - COALESCE(NEW.spent_amount, 0)
+            WHERE transaction_account_id = NEW.transaction_account_id;
+        END IF;
     END IF;