1 2 3 Previous Next 30 Replies Latest reply on Jul 18, 2018 5:04 PM by Sven W.

    Need help on wrting efficient triggers.

    user10151069

      I have a task to create application trigger and need help to write efficient triggers with exceptions.

       

      This is my uncompleted trigger. Version Oracle 12c on windows 10 64bits.

       

      Reason: On insert of record in a table T1 need trigger to fire and do an update statement on the inserted record with exception. C_name column data has to be updated on Test_lead table with which C_name loggedin.

       

      CREATE OR REPLACE TRIGGER TESTLEAD_AfterInsert
      AFTER INSERT
         ON scott.TEST_LEAD
         FOR EACH ROW

       

      DECLARE

         v_cname varchar2(100);

       

      BEGIN

         -- Find username of person performing the INSERT into the table
         SELECT SYS_CONTEXT ('userenv','session_user') INTO v_cname FROM dual;

         -- Update record into same table
         UPDATE scott.TEST_LEAD set c_name = v_cname;
      END;

      /

       

      would the above work. Need help on writing an exception on failure and tips to improve the above code.

        • 1. Re: Need help on wrting efficient triggers.
          John Thorton

          user10151069 wrote:

           

          I have a task to create application trigger and need help to write efficient triggers with exceptions.

           

          This is my uncompleted trigger. Version Oracle 12c on windows 10 64bits.

           

          Reason: On insert of record in a table T1 need trigger to fire and do an update statement on the inserted record with exception. C_name column data has to be updated on Test_lead table with which C_name loggedin.

           

          CREATE OR REPLACE TRIGGER TESTLEAD_AfterInsert
          AFTER INSERT
          ON scott.TEST_LEAD
          FOR EACH ROW

           

          DECLARE

          v_cname varchar2(100);

           

          BEGIN

          -- Find username of person performing the INSERT into the table
          SELECT SYS_CONTEXT ('userenv','session_user') INTO v_cname FROM dual;

          -- Update record into same table
          UPDATE scott.TEST_LEAD set c_name = v_cname;
          END;

          /

           

          would the above work. Need help on writing an exception on failure and tips to improve the above code.

          is application 3-tier?

           

          Consider replacing SELECT with statement below

           

          V_CNAME := SYS_CONTEXT ('userenv','session_user');

           

          As written EVERY row in SCOTT.TEST_LEAD table will be changed.

          I suspect that not every row should be changed.

           

          PLEASE read & follow content of URL below

           

          How do I ask a question on the forums?

           

          Why are NONE of your threads marked as ANSWERED?

           

          user10151069

          • 2. Re: Need help on wrting efficient triggers.
            mathguy

            John Thorton wrote:

             

             

            Consider replacing SELECT with statement below

             

            V_CNAME := SYS_CONTEXT ('userenv','session_user');

             

             

            Even that is too much; there is no need for the V_CNAME variable (and therefore no need for a DECLARATIONS section at all). The UPDATE can simply be

             

            UPDATE ...    SET ... = SYS_CONTEXT(...)

            • 3. Re: Need help on wrting efficient triggers.
              mathguy

              You will have to explain more about the "exception" part. What specific exception or exceptions do you expect and must handle?

               

              As for the "update", the simplest way to achieve what you seem to want is a BEFORE insert trigger. (Best way would be to avoid triggers altogether, but that's another story.)

              Something like this:

               

              create or replace trigger <trigger_name>

                before insert

                on <table_name>

                for each row

              begin

                :new.c_name :=  sys_context ('userenv','session_user');

              end;

              /

              • 4. Re: Need help on wrting efficient triggers.
                AndrewSayer

                As Mathguy said, if you want to set the value of a column for the triggering row then just use :new.column_name = value. And don't bother with the silly select from dual.

                 

                "would the above work. Need help on writing an exception on failure and tips to improve the above code."

                What exceptions are you expecting? You should only be handling expected exceptions that you can legitimately handle - we will have no idea what your business requirements are so we can't really answer.

                 

                If I were you though, I'd think that if this trigger is to be useful then it must be firing for every insert, if it can't succeed then it should raise an exception to the insert statement with the error so that a code fix can be made. This requires zero change to the code that Mathguy shared. I would be alarmed if you'd want to swallow an error and pretend everything was okay to the insert statement.

                • 5. Re: Need help on wrting efficient triggers.
                  Billy~Verreynne

                  This statement:

                  UPDATE scott.TEST_LEAD set c_name = v_cname;
                  

                   

                  .. has no predicate - which means every single row in the table will be updated.

                  • 6. Re: Need help on wrting efficient triggers.
                    BEDE

                    Are triggers "efficient"? I think not. I'd generally opt for using procedures that perform the DML as needed and avoid triggers as much as possible.

                    Some insert select or massive update or merge would perform much worse if there are triggers and I think it's best to do all that is to be done in few SQL statements avoiding lots of context switching between SQL and PL/SQL.

                    • 7. Re: Need help on wrting efficient triggers.
                      AndrewSayer

                      BEDE wrote:

                       

                      Are triggers "efficient"? I think not. I'd generally opt for using procedures that perform the DML as needed and avoid triggers as much as possible.

                      Some insert select or massive update or merge would perform much worse if there are triggers and I think it's best to do all that is to be done in few SQL statements avoiding lots of context switching between SQL and PL/SQL.

                      We have a trigger on every one of our tables for an application which is used by about 15K users daily. Dropping the triggers would result in no difference to response time for a user call. A few context switches for a user call is nothing. It’s Okay, no, it’s 100% normal to do context switches just don’t do it millions of times for a user call. If your user call updates one or two rows (and let’s face it, that’s probaby all a non-batch process should be doing) then getting worked up about context switches is quite silly.

                       

                      Triggers have a nasty reputation due to abusive code (like OP generously demoed), but in reality, they’re perfectly fine if you write them well. The only noticeable impact would be during bulk processing when you’re really hitting it hard, and in those controlled circumstances its very normal to be doing things like do the work of the trigger yourself and not letting it fire (which is incredibly easy when you enable EBR and have your triggers on your editioning views only). Context switching is not your only worry for bulk processes, the existence of a trigger that must be fired will switch your redo writing to single row mode, which could be very bad.

                       

                      But again, in OLTP perfectly fine and should not be feared unless you’ve written some silly logic.

                       

                      By all means go down the route of only allowing DML to a table via known procedures, but don’t write  restrictive procedures that only handle very simple operations (Table API - TAPI) you should be writing procedures that can fully execute your transactions (transactional API - XAPI), otherwise youre just doing the same thing you tried to not do - row by row calls to procedures!

                      • 8. Re: Need help on wrting efficient triggers.
                        Sven W.

                        Since you are on a 12c database you can replace your trigger with a column that uses DEFAULT values.

                         

                        I wrote an article some time ago that covers most of the performance considerations. In essense a trigger based solution will be slower when A LOT of inserts are done. For typical applications you will not notice a difference (allthough there will be one).

                        https://svenweller.wordpress.com/2016/02/24/sequence-and-audit-columns-with-apex-5-and-12c/

                         

                        Here is the optimized example table. Since you are probably not using apex, we can remove some of the code that I had in the blog post.

                         

                        create sequence swe_demo_seq cache 20;

                        create table swe_demo

                          (id number default swe_demo_seq.nextval primary key

                           ,col1 number

                           ,col2 varchar2(30)

                           ,inserted_date date default sysdate not null

                           ,inserted_from varchar2(128) default sys_context('userenv','session_user') not null

                        );

                        1 person found this helpful
                        • 9. Re: Need help on wrting efficient triggers.
                          BEDE

                          So, as I thought in OLAP, triggers should not be used. In OLTP they may be used...

                          Thanks.

                          • 10. Re: Need help on wrting efficient triggers.
                            EdStevens

                            user10151069 wrote:

                             

                            I have a task to create application trigger and need help to write efficient triggers with exceptions.

                             

                            This is my uncompleted trigger. Version Oracle 12c on windows 10 64bits.

                             

                            Reason: On insert of record in a table T1 need trigger to fire and do an update statement on the inserted record with exception. C_name column data has to be updated on Test_lead table with which C_name loggedin.

                             

                            CREATE OR REPLACE TRIGGER TESTLEAD_AfterInsert
                            AFTER INSERT
                            ON scott.TEST_LEAD
                            FOR EACH ROW

                             

                            DECLARE

                            v_cname varchar2(100);

                             

                            BEGIN

                            -- Find username of person performing the INSERT into the table
                            SELECT SYS_CONTEXT ('userenv','session_user') INTO v_cname FROM dual;

                            -- Update record into same table
                            UPDATE scott.TEST_LEAD set c_name = v_cname;
                            END;

                            /

                             

                            would the above work. Need help on writing an exception on failure and tips to improve the above code.

                            Looks like your intent is to simply force the username into the column c_name.  As already pointed out, your UPDATE is not qualified, so you will be making that update to every row in the table, every time.  But beyond that you don't even need (or want) an update statement.  Trying to perform an update on the row firing the trigger should generate a mutating row error.  No, what you want is to simply assign a new value to to row as it is being inserted.

                             

                            CREATE OR REPLACE TRIGGER TESTLEAD_BeforeInsert

                            BEFORE INSERT

                            ON scott.TEST_LEAD

                            FOR EACH ROW

                            BEGIN

                              :new.c_name := SYS_CONTEXT ('userenv','session_user')

                            END;

                            • 11. Re: Need help on wrting efficient triggers.
                              jaramill

                              It's considered RUDE to not mark your thread "answered" if you truly did get an answer.  It's like not saying "Thank you".

                              Though you're new and have relatively few threads, there all unanswered.

                               

                              • 12. Re: Need help on wrting efficient triggers.
                                user10151069

                                Thanks everyone for your valuable tips and suggestions.

                                 

                                Our OLTP application cannot insert user login information and initially we thought that we can insert a dummy value and then the trigger with update statement shall do the rest. I wasn't willing to add trigger as the team suggested.

                                Now that I got the idea from Sven W. I think this is doable.

                                 

                                Insert into scott.table (C1,C2,C3) values (1,2,'sys_context('userenv','session_user')');

                                If the above is doable then I don't need a trigger.

                                • 13. Re: Need help on wrting efficient triggers.
                                  John Thorton

                                  user10151069 wrote:

                                   

                                  Thanks everyone for your valuable tips and suggestions.

                                   

                                  Our OLTP application cannot insert user login information and initially we thought that we can insert a dummy value and then the trigger with update statement shall do the rest. I wasn't willing to add trigger as the team suggested.

                                  Now that I got the idea from Sven W. I think this is doable.

                                   

                                  Insert into scott.table (C1,C2,C3) values (1,2,'sys_context('userenv','session_user')');

                                  If the above is doable then I don't need a trigger.

                                  if application is 3-tier, above does NOT capture end-user name.

                                  • 14. Re: Need help on wrting efficient triggers.
                                    Frank Kulash

                                    Hi,

                                    user10151069 wrote:

                                     

                                    ...

                                    Now that I got the idea from Sven W. I think this is doable.

                                     

                                    Insert into scott.table (C1,C2,C3) values (1,2,'sys_context('userenv','session_user')');

                                    If the above is doable then I don't need a trigger.

                                    In any event, you don't want the outer quotes.

                                    Insert into scott.table (C1, C2, C3) values (1, 2, sys_context ('userenv', 'session_user'));

                                    1 2 3 Previous Next