5 Replies Latest reply: Apr 22, 2012 11:13 PM by rp0428 RSS

    During Excel Macro upload oracle trigger is not working .

    831674
      Hi Experts,

      While i am running Excel macros on top of oracle table ,
      there is one trigger ,before insert on table for each row,

      This trigger will insert same data in to some other table , before inserting excel upload data in to specified table.

      Macro is running fine . and it is inserting data into specified table .But trigger is not working , it is not inserting data on another table .

      Please tell me , what is the cause of this problem ..

      Thanks in advance ,
      Sanjeev.
        • 1. Re: During Excel Macro upload oracle trigger is not working .
          sb92075
          user13483989 wrote:
          Hi Experts,

          While i am running Excel macros on top of oracle table ,
          there is one trigger ,before insert on table for each row,

          This trigger will insert same data in to some other table , before inserting excel upload data in to specified table.

          Macro is running fine . and it is inserting data into specified table .But trigger is not working , it is not inserting data on another table .

          Please tell me , what is the cause of this problem ..
          Either error of omission or error of commission,
          but can't say more without seeing actual trigger code.

          My car won't go.
          Tell me how to make my car go.


          Handle:     user13483989
          Status Level:     Newbie (15)
          Registered:     Jan 16, 2011
          Total Posts:     183
          Total Questions:     80 (67 unresolved)

          WHY so MANY unanswered questions?

          Edited by: sb92075 on Apr 22, 2012 10:40 AM
          • 2. Re: During Excel Macro upload oracle trigger is not working .
            Seberg
            Sanjeev;


            What version of OS and Oracle ?

            Excel macros ( How many? and nobody on the forum can see them )

            Insert trigger ( What does it look like ? )

            Did the Insert trigger ever work ? Outside of Excel does it work?


            Nobody can answer your question because you have not provided enough information.


            And the why so many old, open questions from you is a fair one. Please clean them up.
            • 3. Re: During Excel Macro upload oracle trigger is not working .
              Tubby
              user13483989 wrote:
              Macro is running fine . and it is inserting data into specified table .But trigger is not working , it is not inserting data on another table .
              Check the trigger, ensure it is enabled and that it should be firing for what your macros are doing.

              Trace all the code that gets invoked by the trigger, look for any blocks such as
              exception when others then
              and remove them.
              • 4. Re: During Excel Macro upload oracle trigger is not working .
                831674
                Hi All ,

                We are using

                oracle version : Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

                os version : Linux blade-6 2.6.18-53.el5 #1 SMP Sat Nov 10 19:37:22 EST 2007 x86_64 x86_64 x86_64 GNU/Linux.

                I am trying to upload only one record throw excel upload .

                and trigger code like ,

                CREATE OR REPLACE TRIGGER "TR_BIC_UPLOAD"
                BEFORE INSERT ON ISTB_BIC_CBR_UPLOAD
                FOR EACH ROW
                Declare
                l_Esn Cstbs_Ext_Contract_Stat.External_Seq_No%Type := 0;
                Begin

                Debug.Pr_Debug('ST', 'In trigger cytm_upld_defn');

                Select Decode(:New.Action_Code, Null, 'NEW', :New.Action_Code)
                Into :New.Action_Code
                From Dual;

                Insert Into Sttb_Upload_Master
                (Maintenance_Seq_No
                ,Branch_Code
                ,Source_Code
                ,Source_Seq_No
                ,Maintenance_Type
                ,Upload_Status
                ,Upload_Initiation_Date
                ,Action_Code
                ,User_Id)
                Values
                (:New.Maintenance_Seq_No
                ,global.head_office
                ,:New.Source_Code
                ,:New.Source_Seq_No
                ,'ISDBICUP'
                ,'U'
                ,Sysdate
                ,Decode(:New.Action_Code, Null, 'NEW', :New.Action_Code)
                ,'SYSTEM'
                );

                Exception
                When Others Then
                Dbms_Output.Put_Line(Sqlerrm);
                Raise_Application_Error(-20001, 'Error while updating cstb_ext_contract_master' || Sqlerrm);

                End TR_BIC_UPLOAD;


                And this trigger in normal cases it is working fine . In excel uplaod only it is failing ..

                Please give advice any one ..

                Thanks in advance ,
                Sanjeev.
                • 5. Re: During Excel Macro upload oracle trigger is not working .
                  rp0428
                  >
                  In excel uplaod only it is failing
                  >
                  Failing how? By not inserting a record? Or by falling into the exception handler?

                  Where does this value come from?
                  global.head_office
                  It isn't defined anywhere in the trigger.

                  And why are you doing this DECODE again?
                  Decode(:New.Action_Code, Null, 'NEW', :New.Action_Code)
                  You already adjusted :new.Action_Code at the start of the trigger so just use the value.