mutating trigger problem

[MSobol]

I create a trigger which is now giving a mutating error.
Here is the trigger:
CREATE GLOBAL TEMPORARY TABLE gtt_ai_fin_transaction
(
transaction_nbr number null,
gl_date date null,
transaction_amount number(18,6) null,
discount_taken_amount number(18,6) null,
after_balance_amount number(18,6) null,
after_status char(1) null
)
ON COMMIT DELETE ROWS
tablespace temp
/

CREATE or replace TRIGGER ai_fin_transaction
AFTER INSERT ON fin_transaction
REFERENCING NEW AS NEW
FOR EACH ROW
DECLARE TransactionNumber number;
TransactionAmount number(18,6);
DiscountTakenAmount number(18,6);
AfterBalanceAmount number(18,6);
InvoiceNbr number;

CURSOR transactions IS
WITH MaxGLdate (invoice_nbr,gl_date) AS
(SELECT ft.invoice_nbr,
MAX(COALESCE(ft.gl_date,ft.transaction_date))
FROM fin_transaction ft
WHERE ft.invoice_nbr = :new.invoice_nbr
GROUP BY ft.invoice_nbr
)
SELECT :new.invoice_nbr
FROM dual
JOIN MaxGLdate mgld
ON :new.invoice_nbr = mgld.invoice_nbr
WHERE :new.gl_date < mgld.gl_date;

CURSOR recalculate IS
SELECT transaction_nbr,
transaction_amount,
discount_taken_amount
FROM gtt_ai_fin_transaction
ORDER BY gl_date,
transaction_nbr;

BEGIN

IF (:new.invoice_nbr IS NOT NULL) THEN

UPDATE fin_invoice
SET last_transaction_date = :new.transaction_date
WHERE invoice_nbr = :new.invoice_nbr;

END IF;

IF (:new.payment_nbr IS NOT NULL) THEN

UPDATE fin_payment
SET last_transaction_date = :new.transaction_date
WHERE payment_nbr = :new.payment_nbr;

END IF;

OPEN transactions;

LOOP

FETCH transactions INTO InvoiceNbr;
EXIT WHEN transactions%NOTFOUND;

DELETE FROM gtt_ai_fin_transaction;

INSERT INTO gtt_ai_fin_transaction
SELECT transaction_nbr,
COALESCE(gl_date,transaction_date),
CASE WHEN transaction_type IN ('PAY','CON','CRD','PAD','ADJ','ACC')
THEN transaction_amount * -1
ELSE transaction_amount
END,
CASE WHEN transaction_type IN ('PAY','CON','CRD','PAD','ADJ','ACC')
THEN discount_taken_amount * -1
ELSE discount_taken_amount
END,
0.00,
NULL
FROM fin_transaction
WHERE invoice_nbr = InvoiceNbr
AND payment_nbr IS NOT NULL
ORDER BY COALESCE(gl_date,transaction_date),
transaction_nbr;

SELECT after_balance_amount
INTO AfterBalanceAmount
FROM fin_transaction
WHERE invoice_nbr = InvoiceNbr
AND payment_nbr IS NULL;

OPEN recalculate;

LOOP

FETCH recalculate INTO TransactionNumber,
TransactionAmount,
DiscountTakenAmount;
EXIT WHEN recalculate%NOTFOUND;

AfterBalanceAmount := AfterBalanceAmount + TransactionAmount + DiscountTakenAmount;

UPDATE gtt_ai_fin_transaction
SET after_balance_amount = AfterBalanceAmount,
after_status = CASE WHEN AfterBalanceAmount = 0.00
THEN 'C'
ELSE 'O'
END
WHERE transaction_nbr = TransactionNumber;

END LOOP;

UPDATE fin_transaction
SET fin_transaction.after_balance_amount =
(SELECT after_balance_amount
FROM gtt_ai_fin_transaction
WHERE fin_transaction.transaction_nbr = gtt_ai_fin_transaction.transaction_nbr
),
fin_transaction.after_status =
(SELECT after_status
FROM gtt_ai_fin_transaction
WHERE fin_transaction.transaction_nbr = gtt_ai_fin_transaction.transaction_nbr
)
WHERE fin_transaction.transaction_nbr = gtt_ai_fin_transaction.transaction_nbr;

END LOOP;

END;

The trigger compiles ok with no errors;
When I try to test it using this data:
insert into fin_transaction (TRANSACTION_NBR,invoice_nbr,transaction_amount,transaction_type,transaction_date,after_balance_amou nt,after_status,financial_source)
values (319,999,10000.00,'INV',SysDate,10000.00,'O','A/R')
;
I get this error:
Error starting at line : 1 in command -
insert into fin_transaction (TRANSACTION_NBR,invoice_nbr,transaction_amount,transaction_type,transaction_date,after_balance_amou nt,after_status,financial_source)
values (319,999,10000.00,'INV',SysDate,10000.00,'O','A/R')
Error report -
SQL Error: ORA-04091: table GRAIND16.FIN_TRANSACTION is mutating, trigger/function may not see it
ORA-06512: at "GRAIND16.AI_FIN_TRANSACTION", line 8
ORA-06512: at "GRAIND16.AI_FIN_TRANSACTION", line 47
ORA-04088: error during execution of trigger 'GRAIND16.AI_FIN_TRANSACTION'
04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
*Cause: A trigger (or a user defined plsql function that is referenced in
this statement) attempted to look at (or modify) a table that was
in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.

I can run the SQL statements individually and do not have any problems.
I have examined the trigger and, for the life of me, I don't see where the problem is.
Can anyone assist me???

Thanks
Murray

[gandolf989]

ORA-04088: error during execution of trigger 'GRAIND16.AI_FIN_TRANSACTION'
04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
*Cause: A trigger (or a user defined plsql function that is referenced in
this statement) attempted to look at (or modify) a table that was
in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.

The error message is telling you everything that you need to know. You should not use a trigger for
this because in a row by row trigger the only data that you can look at it the data for the one row
your are affecting at that moment. Generally with a temporary table you load the table with data,
then you run some process against all of the data in the temp table.

So you need to change the procedure to not be a trigger, load all of the data that you want, then
manipulate the data finally commit. When you commit all of the data will go away. An example on
using a global temporary table would be aggregate the changes to the table in another table.
You would have an after insert by row trigger that would insert all of the data into a temporary
table, then an after statement trigger would aggregate the data and insert into a aggregated table
and when you commit the data in the temp table would be deleted.

IMHO, You need to go back and think about what you are really trying to do and find a simpler way to do it.
Also even if it did work, it would be horribly inefficient.