Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I converted the trigger into a "Compound" trigger; this is my first attempt using a compound trigger. Here is my new code: --select * from user_errors;

CREATE OR REPLACE TRIGGER ai_fin_transaction
FOR INSERT
ON fin_transaction
COMPOUND TRIGGER
   TYPE invoice_nbr_t IS TABLE OF fin_transaction.invoice_nbr%TYPE INDEX BY BINARY_INTEGER;
   l_invoice_nbr invoice_nbr_t;
   TransactionNumber               number;
   TransactionAmount               number(18,6);
   DiscountTakenAmount             number(18,6);
   AfterBalanceAmount              number(18,6);
   InvoiceNbr                      number;

   BEFORE STATEMENT IS
     CURSOR C 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 = invoice_nbr
          GROUP BY ft.invoice_nbr
        )
     SELECT invoice_nbr
       FROM dual
            JOIN MaxGLdate mgld
              ON invoice_nbr = mgld.invoice_nbr
      WHERE gl_date < mgld.gl_date;

     BEGIN
       FOR I IN C
       LOOP
         l_invoice_nbr(i.invoice_nbr):=i.invoice_nbr;
       END LOOP;

     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;

     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 BEFORE STATEMENT;

   AFTER EACH ROW IS
   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;

    UPDATE (SELECT ft.after_balance_amount     AS ft_balance,
                   gtt.after_balance_amount    AS gtt_balance,
                   ft.after_status             AS ft_status,
                   gtt.after_status            AS gtt_status
              FROM fin_transaction ft
                   JOIN gtt_ai_fin_transaction gtt
                     ON ft.transaction_nbr = gtt.transaction_nbr
           )
       SET ft_balance = gtt_balance,
           ft_status = gtt_status;

   END AFTER EACH ROW;

END;

It compiles ok but when I run it I get this error:

Connecting to the database Oracle D16.
ORA-01403: no data found
ORA-01403: no data found
ORA-06512: at "GRAIND16.AI_FIN_TRANSACTION", line 52
ORA-04088: error during execution of trigger 'GRAIND16.AI_FIN_TRANSACTION'
ORA-06512: at line 3
Process exited.
Disconnecting from the database Oracle D16.

Can someone assist me with this; I'm venturing into "unknown waters">

Murray

share|improve this question
    
Please fix the formatting first. –  Colin 't Hart Apr 30 at 18:33
    
There's no join, hence invalid. You can't just use TABLENAME.COLUMNNAME and pray Oracle knows what you mean. Use an updatedatable inline view to do the update –  Phil Apr 30 at 19:16
    
More fundamentally, even if you get your syntax errors resolved, a row level trigger on fin_transaction cannot generally query the fin_transaction table. You're going to get a mutating table exception at runtime. That strongly implies that you need to rethink your approach to whatever problem you're actually having. –  Justin Cave Apr 30 at 19:26
    
As Justin says, you're going to hit an ORA-04091. This can be worked around using both a before and after trigger, but it's not pretty. –  Phil Apr 30 at 19:48
    
Are you from a SQL Server/Sybase background? I ask because Temporary tables aren't used very much in Oracle [in my experience] compared to SQL Servef & Sybase –  Phil Apr 30 at 20:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.