1 2 3 Previous Next 30 Replies Latest reply on Jul 18, 2018 5:04 PM by Sven W. Go to original post
      • 15. Re: Need help on wrting efficient triggers.
        Solomon Yakobson

        user10151069 wrote:

         

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

         

        Well, depends. Most applications use database connection pooling where all pool sessions are connected to database using same database ID making

         

        sys_context('userenv','session_user')

         

        irrelevant. Also:

         

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

         

        makes no sense since it simply inserts string 'sys_context('userenv','session_user')' and not login username. Perhaps you meant:

         

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

         

        SY.

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

          Jaramill, I am new to this forum and its not intentional.

           

          If you read the posts I never refrain from appreciating the experts who chime in with their tips and suggestions. Here after I shall pay more attention in marking my threads appropriately.

           

          Thank you for bringing this up else I may not be aware.

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

            user10151069 wrote:

             

            Jaramill, I am new to this forum and its not intentional.

             

            If you read the posts I never refrain from appreciating the experts who chime in with their tips and suggestions. Here after I shall pay more attention in marking my threads appropriately.

             

            Thank you for bringing this up else I may not be aware.

            Hence why we have a link called a FAQ (Frequently Asked Questions) SQL and PL/SQL FAQ and it has a list of links.

             

            One is on how to ask a question on the forums Re: 2. How do I ask a question on the forums?

             

            So good that understood, and it helps the knowledge base be current and helps other forum members.  Welcome to the Oracle SQL & PL/SQL Developer community.

            • 18. Re: Need help on wrting efficient triggers.
              ToolTimeTabor

              Jaramill,

               

              LOL!  RUDE?

               

              I have seen a lot of rudeness on this forum.  More often that not it is directed at the person asking for help.  Did he say "Thanks" in any of his previous his comments?

               

              Is it possible that some, or even all, of his posts were never fully answered.  Maybe, he didn't think it was good to mark them that way without a clear solution?  Or maybe, he is new to forums and never got a forum etiquette lesson? 

               

              The "Answered"  is best used when it will help future users.  If a question is fully, clearly answered, so that a future user could get both a problem and solution, then it should be mark as answered.  Sometimes, if you end up answering your own question or it makes sense to summarize the various inputs into a cohesive post that can be marked as answered.  Other times it makes sense to thank the folks that help and let it drop off the queue, never to be seen again.

               

              Rob

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

                I'm just pointing it out TO the OP and he mentioned he's new and I appreciated that.  Most OPs are rude and don't follow directions and expect the forum members to write their code for them.

                Yes this forum can get rude especially to newbies. Hence why we have FAQs, but when we direct them to the FAQ, they simply ignore it.  So thus, I personally don't waste time in trying to help

                 

                I agree that if the OP did not get answer then yes it should be left unanswered but there are TONS of users that do get an answer and disappear into the ether, only to later RE-appear asking

                yet another question and yet again NOT following the forum rules/etiquette.

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

                  Hi user10151069,

                   

                  my idea was to change your table so that it accepts a default value for this column.

                  For example like this:

                   

                  ALTER TABLE scott.yourTable

                  modify (C3 default sys_context('userenv','session_user') not null);

                   

                  Then you would simply not mention the column during the insert.

                   

                  e.g.

                  insert into scott.yourTable (C1,C2) values (1,2);

                  Alternativly you can use DEFAULT keyword during the insert.

                   

                  insert into scott.yourTable (C1,C2,C3) values (1,2,default);

                  Both options would result in the same thing. The current user (based upon database login) is inserted in column C3.

                   

                  You can also set the default with ON NULL.

                  as in

                   

                  ALTER TABLE scott.yourTable

                  modify (C3 default ON NULL sys_context('userenv','session_user') not null);

                  Which means you can then also insert NULL and null is replaced by the default value.

                  insert into scott.yourTable (C1,C2,C3) values (1,2,null);

                   

                  Second issue: As others already mentioned. the session_user is the database user that does the insert. If you have some kind of connection pooling, then probably all real world users use the same database user to connect to your database. You then need to find a different way to identify who does the insert. Most application tiers do / can set the client_identifier.

                   

                  In such a case try to store this:

                  coalesce(sys_context('userenv','client_identifier'),sys_context('userenv','session_user'))

                   

                  The session_user would be the fallback scenario, if no client_identifier is set. However be aware that this might be a security thing. In theory the client can set this identifier as he/she wants. In reality it is usually done by the application tier.

                  1 person found this helpful
                  • 21. Re: Need help on wrting efficient triggers.
                    rp0428

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

                    Can you explain how that meets OPs stated requirement?

                    C_name column data has to be updated on Test_lead table with which C_name loggedin.

                    I don't see how a 'default' value will replace a value supplied by a user in an insert statement which is what OP said they want to do.

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

                      Which part of my sample code is not clear or doesn't work?

                       

                      >>I don't see how a 'default' value will replace a value supplied by a user in an insert statement which is what OP said they want to do.

                       

                      Ah now I understand. You seem to interpret that the end user supplies a value already. But that is not what op said.

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

                        Thank you Sven W.

                         

                        Your solution works and I don't need trigger to get this to working. Appreciated.

                         

                        All the experts who chimed in, did provide me the awareness to approach this in different ways. Thanks all for your time and reading.

                        • 24. Re: Need help on wrting efficient triggers.
                          rp0428

                          Ah now I understand. You seem to interpret that the end user supplies a value already. But that is not what op said.

                          Yes - I 'interpret' that OP has NO WAY to control what columns/values a user might use in an insert statement.

                           

                          While I certainly agree that a 'default' method will work if a value is NOT supplied it won't help at all if a value, intentionally or accidentally, is supplied.

                           

                          And, IMO, that hole needs to be plugged. Otherwise anyone could 'break' that solution using a simple insert providing a value of 'daffy duck'.

                           

                          Good luck tracking down the user that did that insert. Since tracking the user involved is goes to the heart of what OP ask about I don't really consider a solution with holes in it to be much of a solution.

                           

                          Of course - that is up to OP to decide. I'm not being critical of your 'default' solution - just saying I  don't see it as appropriate for OPs use case.

                           

                          It certainly wouldn't pass muster at any org I have worked with - we try to avoid incomplete solutions whenever possible.

                          • 25. Re: Need help on wrting efficient triggers.
                            Mike Kutz

                            Sven W. wrote:

                             

                            In such a case try to store this:

                            coalesce(sys_context('userenv','client_identifier'),sys_context('userenv','session_user'))

                             

                            Just for Reference:  If you use APEX ( 5.0+ ), you would access APEX$SESSION and APP_USER

                            eg

                            coalesce(sys_context('userenv','client_identifier') -- some other application
                                    ,sys_context('APEX$SESSION', 'APP_USER') -- APEX 5.0+
                                    ,sys_context('userenv','session_user')) -- Oracle USER (eg SQL*Plus/SQL*Developer)
                            

                             

                            MK

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

                              user10151069 wrote:

                               

                              Thank you Sven W.

                               

                              Your solution works and I don't need trigger to get this to working. Appreciated.

                               

                              All the experts who chimed in, did provide me the awareness to approach this in different ways. Thanks all for your time and reading.

                              Glad Sven's post helped.  If it truly is a solution, then mark the thread as answered for future viewings.

                              • 27. Re: Need help on wrting efficient triggers.
                                ToolTimeTabor

                                Jaramill,

                                 

                                I agree that users should mark "Answered" when they get a resolution, because that helps prioritize future search engine results, but that's not the same thing as being rude.

                                 

                                Yes this forum can get rude especially to newbies. Hence why we have FAQs, but when we direct them to the FAQ, they simply ignore it.  So thus, I personally don't waste time in trying to help

                                 

                                Just curious, why is a newbie (or even us lower level guys) somehow less deserving of basic civility?  It simply isn't necessary to be rude.  Help if you wish, ignore if you prefer. 

                                 

                                Maybe there is a FAQ, but just because one exists doesn't mean they were able to resolve their problem.  It does not mean that they are lazy or incompetent.  Not everyone is equally skilled in all the disciplines involved in software development.  SQL is a complete discipline unto itself.  Maybe they don't understand or it simply didn't work for them.  Just because we think the FAQ is adequate, doesn't mean it is.  As for wasting people's time, if one doesn't have anything nice (or helpful) to say, then don't say anything at all.  Move to a more worthy post.

                                 

                                 

                                ...TONS of users that do get an answer and disappear into the ether

                                 

                                What?  So, as a would be user of this forum, if you are not 100% sure you are going to be coming back, then you can't use the forums?  C'mon.  I have read a lot of threads.  Most that I read include follow ups, acknowledgements of success (or failure) and even thanks and "answered" markups.  You don't have to help, if you think they are unappreciative.  Besides, if they "disappear into the ether" they won't ever offend you again...

                                 

                                Rob

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

                                  It's basic common sense and the forum provides the FAQ for basic things like "how to ask a question on the forum" or "how to improve the performance of my query".

                                  Those links HELP them ask the question properly to increase their chance of getting the right answer for their problem....or it helps them GET their answer.

                                   

                                  People simply IGNORE the rules.  They've been told, time and time again (repeat offenders) and it's highly annoying.

                                  They don't take time to LEARN SQL and want it hand-written to them on a silver platter.

                                   

                                  And when I say they disappear into the ether, I forgot to add that they REAPPEAR and ask a similar question or different one, and repeat the same tactic which is ignore the FAQ rules (which were told to them repeatedly in each thread), then when they do get an answer, they again disappear.

                                   

                                  So people do tend to respond rudely, I agree, I just tend to point out that they are repeat offenders and to remind them to read the FAQ. But when they ignore, then some people just reply rudely.  That's that particular person.

                                  • 29. Re: Need help on wrting efficient triggers.
                                    ToolTimeTabor

                                    Jaramill

                                     

                                    I won't dispute that some/many inquiries are poorly thought out, badly written and even incomprehensible at times.  We can simply ignore those until the come back with a better question.

                                     

                                    People simply IGNORE the rules.

                                    At best, forums have customs and practices.  About the only "rule" is that you don't post profanity or other "unacceptable" material.  There may be practices and "good techniques" for asking a question, but they are not rules.

                                     

                                     

                                    They don't take time to LEARN SQL and want it hand-written to them on a silver platter.

                                     

                                    By definition, the "newbie" is learning.  They are probably also learning about a variety of tools at once and SQL is just one of them.  Not everyone can/will learn on your timeline or to your depth.  I have asked a few questions that did not require a "silver platter" and still met with unnecessary rudeness.

                                     

                                    All I am saying is, nobody learned in kindergarten that being rude was alright.  Current American politics aside, defending rude, uncivil and insulting behavior is to defend a bad position.  While they may be a lesser forms of wrong-doing that result in now physical harm, they are still wrong.  Simply, they are not necessary.  Just move on to the next post if the current one does not meet your standards.

                                     

                                    Signing off this thread...

                                     

                                    Rob