6 Replies Latest reply: Sep 9, 2008 4:28 AM by Smile RSS

    Error in trigger`

    Smile
      Hi
      I've written the following trigger but unable to find where the error is:
      Could you please help me out in this .

      Solved

      Thank you

      Edited by: Smile on Feb 19, 2013 2:16 AM
        • 1. Re: Error in trigger`
          Randolf Geist
          These lines
          7  v_bid_tech_id CBR_BID_MAP_T.BID_TECH_ID%TYPE:=CASE WHEN (INSERTING OR UPDATING) THEN :NEW.BID_
          ECH_ID
            8  WHEN DELETING THEN :OLD.BID_TECH_ID;
            9  v_bid_map_tech_id CBR_BID_MAP_T.BID_MAP_TECH_ID%TYPE:=CASE WHEN (INSERTING OR UPDATING) THEN :
          EW.BID_MAP_TECH_ID
           10  WHEN DELETING THEN :OLD.BID_MAP_TECH_ID
          v_org_id CBR_BID_MAP_T.ORG_ID%TYPE:= CASE WHEN(INSERTING OR UPDATING) THEN :NEW.ORG_ID
           17  WHEN DELETING THEN :OLD.ORG_ID;
          are missing the "END" case keyword.

          Regards,
          Randolf

          Oracle related stuff blog:
          http://oracle-randolf.blogspot.com/

          SQLTools++ for Oracle (Open source Oracle GUI for Windows):
          http://www.sqltools-plusplus.org:7676/
          http://sourceforge.net/projects/sqlt-pp/
          • 2. Re: Error in trigger`
            650184
            DECLARE
            7 v_bid_tech_id CBR_BID_MAP_T.BID_TECH_ID%TYPE:=CASE WHEN (INSERTING OR UPDATING) THEN :NEW.BID_
            ECH_ID
            8 WHEN DELETING THEN :OLD.BID_TECH_ID;
            9 v_bid_map_tech_id CBR_BID_MAP_T.BID_MAP_TECH_ID%TYPE:=CASE WHEN (INSERTING OR UPDATING) THEN :
            EW.BID_MAP_TECH_ID
            10 WHEN DELETING THEN :OLD.BID_MAP_TECH_ID;
            11 vmodtp VARCHAR2(1):=CASE WHEN INSERTING THEN 'I'
            12 WHEN UPDATING THEN 'U'
            13 WHEN DELETING THEN 'D'
            14 ELSE NULL
            15 END;

            where is the begin here? after this end there is a begin and end. You don't need an end here. the trigger syntax is :
            declare

            begin
            end
            • 3. Re: Error in trigger`
              Randolf Geist
              Although it looks a bit unusual/convoluted it's legal as part of the variable initialization in the declaration part.

              Regards,
              Randolf

              Oracle related stuff blog:
              http://oracle-randolf.blogspot.com/

              SQLTools++ for Oracle (Open source Oracle GUI for Windows):
              http://www.sqltools-plusplus.org:7676/
              http://sourceforge.net/projects/sqlt-pp/
              • 4. Re: Error in trigger`
                Smile
                Sovled Thank you

                Edited by: Smile on Feb 19, 2013 2:16 AM
                • 5. Re: Error in trigger`
                  Randolf Geist
                  Sorry, I haven't looked into the remaining part of your code.

                  Your INSERT statement is simply invalid, you can't use both the "VALUES" and the "SELECT" clause. Just get rid of the "VALUES (...)" part if you want to use the INSERT INTO ... SELECT FROM version of INSERT.

                  Check the documentation of the INSERT statement for more details:

                  http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_9014.htm#SQLRF01604

                  Regards,
                  Randolf

                  Oracle related stuff blog:
                  http://oracle-randolf.blogspot.com/

                  SQLTools++ for Oracle (Open source Oracle GUI for Windows):
                  http://www.sqltools-plusplus.org:7676/
                  http://sourceforge.net/projects/sqlt-pp/
                  • 6. Re: Error in trigger`
                    Smile
                    Randolf,

                    Thank you very much...
                    I removed the VALUES clause
                    And now working fine....