RE: problem with a trigger (Oracle 11gR2)
- From: Jackie Brock <J.Brock@xxxxxxxxxxxxx>
- To: "Murray.Sobol@xxxxxxxxxxxx" <Murray.Sobol@xxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 30 Apr 2014 18:44:49 +0000
You need to be selecting from gtt_ai_fin_transaction. As it sits, the code is
looking for something aliased gtt_ai_fin_transaction, and not finding it.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Murray Sobol
Sent: Wednesday, April 30, 2014 12:34 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: problem with a trigger (Oracle 11gR2)
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
