Compound trigger: collecting mutating rows into nested table

Question

I have two tables in my project: accounts and transactions (one-to-many relationship). In every transaction I store the balance of the associated account (after the transaction is executed). Additionally in every transaction I store a value of the transaction.

So I needed a trigger fired when someone adds new transaction. It should check whether new account balance will be correct (old account balance + transaction value = new account balance stored in transaction).

So I was suggested, I should use a compound trigger which would:

in before each row section: save a row's PK (made of two columns) somewhere,
in after statement section: check whether all inserted transactions where correct.

Now I can't find anywhere how could I implement the first point.

What I already have:

CREATE OR REPLACE TRIGGER check_account_balance_is_valid
FOR INSERT
ON Transactions
COMPOUND TRIGGER
  TYPE Modified_transactions_T IS TABLE OF Transactions%ROWTYPE;
  Modified_transactions       Modified_transactions_T;

  BEFORE STATEMENT IS BEGIN
    Modified_transactions := Modified_transactions_T();
  END BEFORE STATEMENT;

  BEFORE EACH ROW IS BEGIN
    Modified_transactions.extend;
    Modified_transactions(Modified_transactions.last) := :NEW;
  END BEFORE EACH ROW;

  AFTER STATEMENT IS BEGIN
    NULL; -- I will write something here later
  END AFTER STATEMENT;
END check_account_balance_is_valid;
/

However, I got that: Warning: execution completed with warning 11/58 PLS-00049: bad bind variable 'NEW'

Could someone tell me, how to fix it? Or maybe my whole "compound trigger" idea is wrong and you have better suggestions.

Update 1

Here is my ddl script: http://pastebin.com/MW0Eqf9J

Not related to the question you asked, but in general i believe triggers are not a good solution. They should be used only when you absolutely cannot modify the applications in the database. In your case, if you have access to all the application code, isn't it possible to add that check in the piece of code that is actually inserting rows into the transactions table?(for example , you have a stored procedure for inserting into transactions table, call a function in that procedure to get this check done) — A Nice Guy

Wernfried Domscheit Wernfried Domscheit · Accepted Answer · 2014-01-29T07:48:07

Maybe try this one:

TYPE Modified_transactions_T IS TABLE OF ROWID;
Modified_transactions       Modified_transactions_T;

BEFORE STATEMENT IS BEGIN
  Modified_transactions := Modified_transactions_T();
END BEFORE STATEMENT;

BEFORE EACH ROW IS BEGIN
  Modified_transactions.extend;
  Modified_transactions(Modified_transactions.last) := :NEW.ROWID;
END BEFORE EACH ROW;

or this

TYPE PrimaryKeyRecType IS RECORD (
   Col1 Transactions.PK_COL_1%TYPE, Col2 Transactions.PK_COL_2%TYPE);
TYPE Modified_transactions_T IS TABLE OF PrimaryKeyRecType;

...

Modified_transactions(Modified_transactions.last) := PrimaryKeyRecType(:NEW.PK_COL_1, :NEW.PK_COL_2);

Compound trigger: collecting mutating rows into nested table

2 Answers