Murray You are trying to set columns in FIN_TRANSACTION using a correlated update from GTT_AI_FIN_TRANSACTION but you haven't actually included a join to the GTT. I think you would need something like this (which you could test for syntax outside your trigger code) UPDATE fin_transaction JOIN gtt_ai_fin_transaction ON fin_transaction.transaction_nbr = gtt_ai_fin_transaction.transaction_nbr SET fin_transaction.after_balance_amount = gtt_ai_fin_transaction.after_balance_amount, fin_transaction.after_status = gtt_ai_fin_transaction.after_ status ; However you may then find yourself tripping over mutating table ORA-04091 (as you are updating the same table that the trigger is defined against). HTH Nigel On 30 April 2014 19:33, Murray Sobol <Murray.Sobol@xxxxxxxxxxxx> wrote: > I am having a problem accessing a Global Temporary table inside a trigger. > Here is my code: > 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 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(ft.gl_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, > gl_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 gl_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 = > gtt_ai_fin_transaction.after_balance_amount, > fin_transaction.after_status = > gtt_ai_fin_transaction.after_status > WHERE fin_transaction.transaction_nbr = > gtt_ai_fin_transaction.transaction_nbr; > > END LOOP; > > END; > > And here is the error: > AI_FIN_TRANSACTION TRIGGER 1 128 50 PL/SQL: ORA-00904: > "GTT_AI_FIN_TRANSACTION"."TRANSACTION_NBR": invalid identifier ERROR > 0 > AI_FIN_TRANSACTION TRIGGER 2 125 9 PL/SQL: SQL > Statement ignored ERROR 0 > > The error is pointing to this "update" statement but I don't understand > why. > UPDATE fin_transaction > SET fin_transaction.after_balance_amount = > gtt_ai_fin_transaction.after_balance_amount, > fin_transaction.after_status = > gtt_ai_fin_transaction.after_status > WHERE fin_transaction.transaction_nbr = > gtt_ai_fin_transaction.transaction_nbr; > > Thanks > Murray Sobol > Murray.sobol@xxxxxxxxxxxx > murrays@xxxxxxxxxxxxxxxxxxxx > > > -- > //www.freelists.org/webpage/oracle-l > > >