1 2 Previous Next 17 Replies Latest reply: May 3, 2012 11:30 PM by Frank Kulash RSS

    Oracle assertions

    934960
      Hello there,

      I'm currently have an issue with my SQL script in which I wanted to create an assert where a clients job proposal is not accepted until their previous jobs have been paid off. However, I learn't that oracle does not support the use of asserts. So, I tried to mimic an assert using a exception trigger but I'm having some difficulties. So far I have:
      CREATE OR REPLACE TRIGGER cust_has_debt
      BEFORE UPDATE ON proposal FOR EACH ROW
      DECLARE
      has_debt EXCEPTION;
      BEGIN
      SELECT clientNumber, CASE WHEN EXISTS (SELECT invoiceNumber FROM invoice 
      WHERE invoiceNumber = :old.proposalNumber AND invoiceStatus = 'Paid')
      THEN RAISE has_debt
      END AS NewFile
      FROM client
      EXCEPTION
      WHEN has_debt THEN
      DBMS_OUTPUT.PUT_LINE('The existing client has outstanding invoices from previous jobs.');
      END;
      /
      The error:
       
      ERROR at line 6: PL/SQL: ORA-00905: missing keyword
      6. SELECT clientNumber, CASE WHEN EXISTS (SELECT invoiceNumber FROM invoice 
      This may be a simple error, but I'm not sure if the above statements would work.

      To help, I provided some information about the tables:
      create table proposal  (
         proposalNumber         INTEGER                         not null,
         clientNumber         INTEGER                         not null,
         status               VARCHAR2(20),
         constraint PK_proposal primary key (proposalNumber)
      );
      
      create table invoice  (
         invoiceNumber          INTEGER                         not null,
         proposalNumber         INTEGER                         not null,
         invoiceStatus               VARCHAR2(20), /* status can be either paid or has debt/not paid */
         constraint PK_invoice primary key (invoiceNumber)
      );
      
      create table client  (
         clientNumber         INTEGER                         not null,
         fullName            VARCHAR2(50),
         contactNumber          INTEGER,
         constraint PK_client primary key (clientNumber)
      );
      So basically, a new job proposal is not accepted from a client unless their previous jobs have been paid off.

      Any help is appreciated!

      Thanks
        • 1. Re: Oracle assertions
          Frank Kulash
          Hi,

          Welcome to the forum!

          Review what a SQL CASE expression is: an expression that supplies a sclar value in one of the SQL data types (such as NUMBER or VARCHAR2). All of the THEN clauses (end the ELSE clause, if there is one) must return an expression of that same data type. See
          http://docs.oracle.com/cd/B28359_01/server.111/b28286/expressions004.htm#sthref2742

          Let's look at the THEN clause of your SQL CASE expression:
          THEN RAISE has_debt
          is "RAISE has_dept" a scalar value in one of the SQL data types? Is it a NUMBER? Is a a VARCHAR2?
          • 2. Re: Oracle assertions
            Solomon Yakobson
            RAISE is PL/SQL statement and can't be used in SQL. And in any case SELECT statement in PL/SQL MUST have INTO clause. And there is no need for exception. Use:
            SQL> CREATE OR REPLACE
              2    TRIGGER cust_has_debt
              3      BEFORE UPDATE
              4      ON proposal
              5      FOR EACH ROW
              6      DECLARE
              7          v_cnt NUMBER;
              8      BEGIN
              9          SELECT  count(*)
             10            INTO  v_cnt
             11            FROM  invoice 
             12            WHERE invoiceNumber = :old.proposalNumber
             13              AND invoiceStatus = 'Paid'
             14              AND rownum = 1;
             15          IF v_cnt = 1
             16            THEN
             17              DBMS_OUTPUT.PUT_LINE('The existing client has outstanding invoices from previous jobs.');
             18          END IF;
             19  END;
             20  /
            
            Trigger created.
            
            SQL> 
            SY.
            • 3. Re: Oracle assertions
              934960
              Thanks Solomon, the trigger is created properly but I'm afraid it does not fire. When I do and insert like so:
              insert into proposal
              select '1002', '102', 'Pending' from dual
              The row gets created even though the customer has an invoice in which they havent paid for.
              • 4. Re: Oracle assertions
                Solomon Yakobson
                931957 wrote:
                the trigger is created properly but I'm afraid it does not fire. When I do and insert like so:
                You create BEFORE UPDATE trigger and expect it to fire on INSERT???

                SY.
                • 5. Re: Oracle assertions
                  JustinCave
                  The trigger does not prevent the row from being inserted. It merely prints a message to the dbms_output buffer, if such a buffer has been created, which may or may not display to the user depending on what tool they're using and their session configuration.

                  If you want to prevent the row from being inserted, the trigger needs to raise an error
                  IF v_cnt = 1
                  THEN
                    RAISE_APPLICATION_ERROR(-20001, 'The existing client has outstanding invoices from previous jobs.');
                  END IF;
                  Justin
                  • 6. Re: Oracle assertions
                    934960
                    Hi,

                    yeah I changed the trigger to be after update so that I can test the insert.

                    Thanks for that justin, but no error is being shown, the row is still be inserted.
                    • 7. Re: Oracle assertions
                      JustinCave
                      Post the exact trigger that you are using.

                      Post the result of running the same query that is in the trigger that shows that there are unpaid invoices for that customer.

                      The query you posted is looking for invoices where the invoiceStatus = 'Paid' which is what Solomon used in his trigger. If you really intend to look for unpaid invoices, you would presumably want to use a different status.

                      Justin

                      Edited by: Justin Cave on May 3, 2012 9:46 PM
                      • 8. Re: Oracle assertions
                        934960
                        Hello Justin,

                        the trigger I'm using now is below with your raise application error suggestion:
                        CREATE OR REPLACE
                        TRIGGER cust_has_debt
                        AFTER UPDATE
                        ON proposal
                        FOR EACH ROW
                        DECLARE
                        v_cnt NUMBER;
                        BEGIN
                        SELECT  count(*)
                        INTO  v_cnt
                        FROM  invoice 
                        WHERE invoiceNumber = :old.proposalNumber
                        AND invoiceStatus = 'Not Paid'
                        AND rownum = 1;
                        IF v_cnt = 1
                        THEN
                        RAISE_APPLICATION_ERROR(-20001, 'The existing client has outstanding invoices from previous jobs.');
                        END IF;
                        END;
                        / 
                        As mentioned running something like:
                         insert into proposal
                        select '1002', '102', 'Pending' from dual
                        will insert the row.

                        Now if I attempt to update the proposal table with status from 'pending' to 'accepted', the trigger doesn't fire raising the error that the customer has outstanding invoices from other jobs, even though my table has test data consisting of the same customer having outstanding invoices.
                        • 9. Re: Oracle assertions
                          Solomon Yakobson
                          931957 wrote:

                          yeah I changed the trigger to be after update so that I can test the insert.
                          Why are you comparing nvoiceNumber to proposalNumber:
                          WHERE invoiceNumber = :old.proposalNumber
                          Also, since you say "proposal is not accepted until their previous jobs have been paid off", it should be:
                          AND invoiceStatus != 'Paid'
                          But main issue is there is no direct relation between proposal and invoice on client level. Your code is looking for an invoice for a NEW proposal so there can't be an invoice for it. You would need to query proposal to get all proposal where client is :NEW.client and check invoice status for all such proposals. But querying triggering table inside a trigger will cause mutating table error unless you will be always inserting proposals using:
                          INSERT INTO proposal VALUES(...);
                          SY.

                          Edited by: Solomon Yakobson on May 3, 2012 10:06 PM
                          • 10. Re: Oracle assertions
                            Solomon Yakobson
                            931957 wrote:

                            Now if I attempt to update the proposal table with status from 'pending' to 'accepted', the trigger doesn't fire raising the error that the customer has outstanding invoices from other jobs, even though my table has test data consisting of the same customer having outstanding invoices.
                            I already pointed out, your trigger compares invoice number to proposal number and on top it will raise an error when invoice status is Paid.

                            SY.
                            • 11. Re: Oracle assertions
                              934960
                              Hello Solomon,

                              The code is the skeleton you provided me with. Also, I already changed the line:
                               AND invoiceStatus = 'Paid' 
                              to
                               AND invoiceStatus = 'Not Paid' 
                              as you can see from my previous post
                              • 12. Re: Oracle assertions
                                Solomon Yakobson
                                931957 wrote:

                                The code is the skeleton you provided me with. Also, I already changed the line:
                                But still comparing invoice number to proposal number. And if there will be a match it will be a wrong one. But even if you change it there will never be a match since as I already mentioned there can't be an invoice for a NEW proposal.

                                SY.
                                • 13. Re: Oracle assertions
                                  934960
                                  Hello Solomon,

                                  I've changed it to
                                   WHERE proposalNumber = :old.proposalNumber 
                                  and your right, the trigger still doesn't fire. I don't understand? If a proposal gets its status updated from 'pending' to 'accepted', it should make sure that the customer related to the proposal has no previous invoices with previous proposal numbers have the status 'not paid'. The trigger should not let this update occur.

                                  I'm looking through the code, and I don't see how it uses a customer number for example, to check previous proposals linked with invoices.
                                  • 14. Re: Oracle assertions
                                    Solomon Yakobson
                                    :OLD is column value before the action, so it is obvious that for INSERT :OLD values are NULL, since there is no before. But:
                                    WHERE proposalNumber = :new.proposalNumber
                                    will not do you any good either. Client makes a proposal and new row is inserted into proposal table. Then you create an invoice, right? So it is obvious there can't be invoice for about to be inserted new proposal. As I said, what you need is to check if invoices for OTHER proposals by same client are all paid. So you need :
                                    SELECT  count(*)
                                    INTO  v_cnt
                                    FROM  invoice 
                                    WHERE proposalNumber IN (SELECT proposalNumber FROM proposal WHERE clientNumber = :new.clientNumber)
                                    AND invoiceStatus = 'Not Paid'
                                    AND rownum = 1;
                                    But again, as I already mentioned, querying triggering table inside a trigger will cause mutating table error unless you will be always inserting proposals using:
                                    INSERT INTO proposal VALUES(...);
                                    SY.
                                    1 2 Previous Next