Re: problem with a trigger (Oracle 11gR2)

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_at_openlink.com> 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_at_openlink.com
> murrays_at_dbcsmartsoftware.com
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l