Question 262633 in PostgreSQL Question posted in
The official documentation can be found here.

How to correct my PostgreSQL in order to correct the syntax error and well use the CREATE TRIGGER statement?

I need to create a PostgreSQL PL/pgSQL code capable of simulating a simple transfer of money from one account to another.
So here is my code:

DROP TABLE clients, accounts, transactions;
 
CREATE TABLE IF NOT EXISTS clients
(
    id int PRIMARY KEY,
    name varchar
);
 
CREATE TABLE IF NOT EXISTS accounts
(
    id int PRIMARY KEY,
    balance float,
    client int,
    FOREIGN KEY (client) REFERENCES clients(id),
    CHECK (balance >= 0)
);
 
CREATE TABLE IF NOT EXISTS transactions
(
    id int PRIMARY KEY,
    payer int,
    recipient int,
    amount float,
    FOREIGN KEY (payer) REFERENCES clients(id),
    FOREIGN KEY (recipient) REFERENCES clients(id)
);
 
 
INSERT INTO clients VALUES (10, 'Client 1');
INSERT INTO clients VALUES (14, 'Client 2');
INSERT INTO clients VALUES (25, 'Client 3');
 
INSERT INTO accounts VALUES (2, 300, 10);
INSERT INTO accounts VALUES (8, 2000, 14);
INSERT INTO accounts VALUES (12, 650, 25);
 
 
 
 
CREATE TRIGGER new_transaction
AFTER INSERT ON transactions
BEGIN
    UPDATE accounts
    SET balance = balance - 50
    FROM clients
    WHERE clients.id = accounts.client AND clients.id = 10;
    UPDATE accounts
    SET balance = balance + 50
    FROM clients
    WHERE clients.id = accounts.client AND clients.id = 14;
END;
 
 
 
 
INSERT INTO transactions VALUES (1, 10, 14, 50);
SELECT * FROM accounts;

But a syntax error prevents it from working…
I tried without BEGIN and END but rather with "FOR EACH STATEMENT" but it also made a syntax error…
Can you help me please ?

Thank you in advance for your answers !

Tags:

2

Answers


  1. 0

    You’d need to create a function to trigger it with;

    CREATE OR REPLACE FUNCTION update_account_balance()
RETURNS TRIGGER AS $$
BEGIN
    -- amount from the payer's balance
    UPDATE accounts
    SET balance = balance - NEW.amount
    WHERE client = NEW.payer;

    -- amount to the recipient's balance
    UPDATE accounts
    SET balance = balance + NEW.amount
    WHERE client = NEW.recipient;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

    and then trigger it via

    CREATE TRIGGER new_transaction
AFTER INSERT ON transactions
FOR EACH ROW
EXECUTE FUNCTION update_account_balance();

    continue your transactions and it will automatically trigger for each row insertions in the transactions table.

    Login or Signup to reply.
  2. 0

    Let’s put aside the code errors you currently have and discuss the design. As currently structured your design works if you introduce the rule that a client can have only 1 account. Why? Well each transactions entry bypasses the accounts table
    going directly clients which then gets accounts for the client – all accounts for the client. This in turn causes transactions to affect all accounts within the transaction for each payer and recipient.
    Example Here.
    The results of 1 transfer for 250.

    1. Client 100 balance increase by 250. Correct.
    2. Client 200 balance decrease by 500. Should be decrease by 250
    3. Total all Balance decrease by 250. Should remain the same.

    Solution: Do not relate transactions to clients, relate them instead to the individual accounts. Something like:

    CREATE TABLE IF NOT EXISTS transactions
(
    id int PRIMARY KEY,
    payer_account int,
    recipient_account int,
    amount float,
    FOREIGN KEY (payer_account) REFERENCES accounts(id),
    FOREIGN KEY (recipient_account) REFERENCES accounts(id)
);

    Alternatively put a unique constraint on client in the accounts table. Thereby restricting a client to a single account.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search