PL/SQL Trigger to update the Account balance for each new transaction entered in oracle-CodePudding

I have two tables:

account (Account_Nbr, Balance) transaction(Account_Nbr, Tx_Amount, Tx_Type_Code)

And I'm trying to create a trigger on the transaction table to update the balance of an account in the account table. There are a few accounts in the account table and I have to either add or subtract the Tx_Amount from them depending on if the Tx_Type_Code is a 'D' for deposit or 'W' for withdrawal. Here is what I have so far:

CREATE OR REPLACE TRIGGER balupdater
AFTER UPDATE ON transaction
FOR EACH ROW
DECLARE
     dollars NUMBER(20);
BEGIN
SELECT Tx_Ammount
  INTO dollars 
  FROM transaction;

UPDATE account a
   SET a.balance = a.balance   dollars
 WHERE a.Account_Nbr = :new.Account_Nbr;
END;

It looks like it should work to me, but nothing happens when I run it. I also get an error saying Tx_Ammount is an invalid identifier

CodePudding user response：

Your main mistake is this:

SELECT Tx_Ammount
  INTO dollars 
  FROM transaction;

Here you select all transactions and try to put their amounts into a single numeric variable. This will raise an exception, when there is more than one transaction in the table.

What you want instead is to work with the one amount of the changed row, which is :new.tx_amount.

Then, your trigger is an update trigger, in which case you should consider the difference between the value before and after the update.

Here is an insert/update trigger that should work for you.

CREATE OR REPLACE TRIGGER balupdater
AFTER INSERT OR UPDATE OF tx_amount, tx_type_code ON transaction
FOR EACH ROW
BEGIN
  UPDATE account a
     SET a.balance = a.balance
         
       CASE WHEN :new.tx_type_code = 'D' THEN :new.tx_amount 
                                         ELSE -:new.tx_amount END
       -
       CASE WHEN :old.tx_type_code = 'D' THEN COALESCE(:new.tx_amount, NULL) 
                                         ELSE -COALESCE(:new.tx_amount, NULL) END
   WHERE a.account_nbr = :new.account_nbr;
END;

CodePudding user response：

You need to add the new amount and subtract the old amount. For INSERTs the old amount will be NULL so you need to use COALESCE (or NVL) to set the value to 0 (and not NULL).

You probably also need to enforce the condition that each transaction's Account_nbr cannot be modified (otherwise you need to reverse the original transaction on the old account and then apply it to the new account).

CREATE OR REPLACE TRIGGER balupdater
AFTER INSERT OR UPDATE OF Account_nbr, Tx_Type_Code, Tx_Ammount ON transaction
FOR EACH ROW
DECLARE
BEGIN
  IF :OLD.account_nbr IS NOT NULL
     AND :NEW.account_nbr <> :OLD.account_nbr
  THEN
    RAISE_APPLICATION_ERROR(
      -20000,
      'Cannot change account numbers for transactions.'
    );
  END IF;
  
  UPDATE account
  SET    balance = balance
                   (DECODE(:NEW.Tx_Type_Code, 'W', -1, 'D', 1, 0)
                    * COALESCE(:NEW.Tx_Ammount, 0))
                 - (DECODE(:OLD.Tx_Type_Code, 'W', -1, 'D', 1, 0)
                    * COALESCE(:OLD.Tx_Ammount, 0))
  WHERE  Account_Nbr = :NEW.Account_Nbr;
END;
/

db<>fiddle here