2 Replies Latest reply: Oct 15, 2012 11:06 AM by rp0428 RSS

    Insert Trigger NOT Firing

    PSULionRP
      I am attempting to Insert to a Table with the following Trigger definition...

      TRIGGER "Trigger_Name"
      BEFORE
      INSERT
      ON TABLE_NAME_1
      REFERENCING NEW AS NEW OLD AS OLD
      FOR EACH ROW
      BEGIN
      IF (:NEW.rate_plan_id IS NULL) OR (:NEW.rate_plan_id = -1) THEN
      SELECT TABLE_NAME_1.NEXTVAL
      INTO :NEW.rate_plan_id
      FROM dual;
      END IF;
      END;

      My Insert statement has a "-1" in the position of the rate_plan_id. Upon Insert, I get the following Oracle Error...

      ERROR UPDATE CPES_CAS_SUPPLIER_RATE_PLAN: ORA-00001: unique constraint (TABLE_NAME_1_RATE_PLAN_PK) violated


      The unique constraint TABLE_NAME_1_RATE_PLAN_PK is, you can probably guess, the rate_plan_id.

      Has the Table become corrupt because someone did not follow the business rules of inserting to this table?

      How can I fix this so the Trigger will fire???

      Any help is GREATLY appreciated and Thanks in advance for your review and am hopeful for a reply.

      PSULionRP
        • 1. Re: Insert Trigger NOT Firing
          Jeff Smith Sqldev Pm-Oracle
          You have a lot of problems. However, I recommend you ask your question in the general SQL/PLSQL forums.

          You need to fix your existing data though. There's nothing wrong with your trigger - at least not based on what you're seeing with the ORA- message.
          • 2. Re: Insert Trigger NOT Firing
            rp0428
            Wrong forum!

            This is the SQL Developer forum and is NOT for SQL/PLSQL questions. Please mark the question ANSWERED and repost in the correct forum at
            PL/SQL

            When you repost you should provide the DDL for the trigger you are actually using. The code you posted won't compile due to the errors in it.
            >
            My Insert statement has a "-1" in the position of the rate_plan_id. Upon Insert, I get the following Oracle Error...

            ERROR UPDATE CPES_CAS_SUPPLIER_RATE_PLAN: ORA-00001: unique constraint (TABLE_NAME_1_RATE_PLAN_PK) violated

            The unique constraint TABLE_NAME_1_RATE_PLAN_PK is, you can probably guess, the rate_plan_id.

            Has the Table become corrupt because someone did not follow the business rules of inserting to this table?

            How can I fix this so the Trigger will fire???
            >
            First the DDL you posted is invalid so won't even compile without error.

            If TABLE_NAME_1 is a table then TABLE_NAME_1.NEXTVAL cannot be he name of a sequence because you would get an error
            >
            ORA-00955: name is already used by an existing object
            >

            When you repost provide the actual DDL you are using or people will not be able to help you.