Changes between Version 2 and Version 3 of WikiStart/Optimization/vtora_verzija

Timestamp:

04/01/25 22:00:03 (2 months ago)

Author:

203206

Comment:

c1

WikiStart/Optimization/vtora_verzija

@@ -62 +62 @@
 
 }}}
+
+
+
+=== Trigger 2
+
+{{{
+
+
+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();
+
+}}}
+