Автоматско ажурирање на баланс на трансакциска сметка

Опис

Овој тригер овозможува автоматско ажурирање на балансот (balance) на трансакциските сметки при секоја промена во табелата transaction_breakdown

Секој запис во transaction_breakdown претставува:

• приход (earned_amount)
• трошење (spent_amount)

Тригерот гарантира дека балансот на сметката секогаш ја рефлектира реалната состојба по секоја трансакција

Табели опфатени со тригерот

• transaction_account
• transaction_breakdown

Тип на тригер

• AFTER INSERT
• AFTER UPDATE
• AFTER DELETE

Активиран над табелата transaction_breakdown

SQL Код

CREATE OR REPLACE FUNCTION update_transaction_account_balance()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- При бришење на запис (rollback на ефектот)
    IF TG_OP = 'DELETE' THEN
        UPDATE transaction_account
        SET balance = balance - OLD.earned_amount + OLD.spent_amount
        WHERE transaction_account_id = OLD.transaction_account_id;

    -- При додавање на нов запис
    ELSIF TG_OP = 'INSERT' THEN
        UPDATE transaction_account
        SET balance = balance + NEW.earned_amount - NEW.spent_amount
        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;
    END IF;

    RETURN NULL;
END;
$$;


CREATE TRIGGER trg_update_transaction_account_balance
AFTER INSERT OR UPDATE OR DELETE
ON transaction_breakdown
FOR EACH ROW
EXECUTE FUNCTION update_transaction_account_balance();

Објаснување на логиката

DELETE:

• Се поништува ефектот од избришаниот запис

INSERT:

• Приходите се додаваат на балансот
• Трошоците се одземаат од балансот

UPDATE:

• Прво се поништува стариот ефект
• Потоа се применува новиот ефект

Причина за користење

Овој тригер е клучен затоа што:

• balance е динамичка и критична вредност
• рачно ажурирање е ризично
• апликацијата не смее да биде единствен извор на вистина
• базата ја контролира финансиската логика
• се елиминираат грешки при ажурирање и бришење