Changes between Version 6 and Version 7 of WikiStart/DB

Timestamp:

03/22/25 21:37:43 (2 months ago)

Author:

203206

Comment:

db4

WikiStart/DB

@@ -108 +108 @@
 
 
+{{{
+CREATE OR REPLACE FUNCTION after_transaction_trigger2()
+RETURNS TRIGGER AS $$
+BEGIN
+    IF NEW.type = 'Deposit' THEN
+        -- Log deposit as a self-transfer
+        INSERT INTO transfer(s_id, r_id)
+        VALUES (NEW.account_id, NEW.account_id);
+
+    ELSIF NEW.type = 'Withdrawal' THEN
+        -- Log withdrawal as a self-transfer
+        INSERT INTO transfer(s_id, r_id)
+        VALUES (NEW.account_id, NEW.account_id);
+
+    ELSIF NEW.type LIKE '(MKD) Transfer Out to%' OR 
+          NEW.type LIKE '(USD) Transfer Out to%' OR 
+          NEW.type LIKE '(EUR) Transfer Out to%' OR 
+          NEW.type LIKE '(GBP) Transfer Out to%' THEN
+          
+        -- Extract recipient username and insert into transfer table
+        INSERT INTO transfer(s_id, r_id)
+        SELECT NEW.account_id, 
+               (SELECT id FROM account 
+                WHERE username = TRIM(SUBSTRING(NEW.type FROM POSITION('Transfer Out to ' IN NEW.type) + 15)) 
+                LIMIT 1);
+
+    END IF;
+    
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER after_transaction2
+AFTER INSERT ON transaction
+FOR EACH ROW
+EXECUTE FUNCTION after_transaction_trigger2();
+
+
+}}}
+