Changes between Version 1 and Version 2 of Trigger1

Timestamp:

06/28/26 16:46:19 (8 hours ago)

Author:

211101

Comment:

--

Trigger1

@@ -30 +30 @@
 LANGUAGE plpgsql
 AS $$
+DECLARE
+    v_transaction_id INT;
 BEGIN
-    UPDATE transaction t
+    IF TG_OP = 'DELETE' THEN
+        v_transaction_id := OLD.transaction_id;
+    ELSE
+        v_transaction_id := NEW.transaction_id;
+    END IF;
+
+    UPDATE "transaction" t
     SET net_amount = (
-        SELECT COALESCE(SUM(tb.earned_amount - tb.spent_amount), 0)
+        SELECT COALESCE(SUM(
+            COALESCE(tb.earned_amount, 0) - COALESCE(tb.spent_amount, 0)
+        ), 0)
         FROM transaction_breakdown tb
         WHERE tb.transaction_id = t.transaction_id
     )
-    WHERE t.transaction_id = COALESCE(NEW.transaction_id, OLD.transaction_id);
+    WHERE t.transaction_id = v_transaction_id;
 
     RETURN NULL;