Sunday, January 4, 2015

Continuing the story

One of my 2015 new year's resolutions, was to finish the story I started on this blog. The story being a talk that I had delivered a couple of times 3-4 years ago on various Oracle/usergroup events. The talk is about why I think triggers should *not* be considered harmful. There is one specific use-case for triggers -- implementing validation code for what's called a multi-row constraint -- that I will treat in-depth on this blog.

So here goes.

Let's first recap what we did sofar:
  • In november 2011 I started this blog and set the scene. I talked about DML-triggers and pointed out some of their features and restrictions. Obviously I introduced the infamous 'mutating table' error ORA-04091 that exists for row-level triggers (for each row). Everybody that starts out using triggers to implement a multi-row constraint, will hit this error.
  • Then in december 2011 I explained in detail what that error is all about, and why it is your friend and not your enemy. I also showed that the common workarounds for this error really aren't workarounds and should be avoided.
  • The month thereafter I demonstrated that using a statement trigger (instead of a row trigger) as a workaround has a serious performance disadvantage.
  • I then took a step back and discussed various other use-cases for DML-triggers. For these other use-cases I agree with the common consensus that triggers are to be considered harmful.
  • Here I specified one use-case for triggers being the implementation of multi-row constraints. And explained how the lack of SQL assertions, forces us to employ triggers to implement these kinds of constraints.
  • Next I discussed the broader topic of constraints, and how they can be meaningfully classified: multi-row constraints being one of the classes.
  • I showed how two other constraint classes (attribute and tuple constraints) ideally should be implemented using a declarative CHECK, and not via a trigger.
  • In the following post I discussed different implementation strategies for multi-row constraints, the vast majority of which we cannot simply declare to the DBMS. We are required to write our own procedural validation code to implement these constraints. One implementation strategy however comes very close to being a declarative one: the use of materialized views that fail to refresh in case a violation for the constraint exists.
  • I then discussed the materialized view strategy in some detail in this post.
And that's where the development of my story is right now. Let's continue. And we will do so by discussing the correct workaround for the mutating table error when you try to implement a multi-row constraint. Recall we have looked at the following workarounds sofar:
  • Workaround 1a: a row-trigger where we wrapped the query accessing the mutating table in an autonomous transaction (thereby preventing ORA-04091).
  • Workaround1b: a row-trigger where we queried the mutating table via a loopback database-link, which also prevents the error.
  • Workaround 2: a statement trigger, which was not an acceptable solution performance-wise. It had to always check (not just when a manager got inserted), and it had to check all departments (not just the one that was affected by the DML-statement).
To implement multi-row constraints correctly and efficiently we need to build a combination of a row-trigger and a statement-trigger. The task of the row-trigger is to convey the information about which rows were affected by the DML-statement, to the after-statement-trigger. This statement-trigger can then use this information and query the mutating table (which it is allowed to do) in an efficient manner.


We can employ three 'container'-types to convey this information:
  1. Use a persistent packaged (table) variable
  2. Use compound triggers and its state variables
  3. Use a global temporary table (gtt)
In all three cases the row-trigger would populate the container with the relevant information to be conveyed to the statement-trigger. And the statement-trigger would inspect the container. Note that in the cases of a packaged variable (1) and a gtt (3) you are to make sure that the container is empty when a new DML-statement starts. The state variables of a compound trigger are initialized (by the DBMS) each time the compound trigger fires, even when the triggering statement is interrupted and restarted. Packaged variables do not rollback when the triggering statement is interrupted. The contents of a gtt do of course. Both packaged and state variables live in the PGA of the session and since they involve memory manipulation are likely to be 'faster' to populate and inspect, than the gtt container is.

However, I personally prefer to use a gtt. Perhaps the only reason being that it supports DML-statements that affect many, many rows. With state and package variables you might run the risk of blowing up the PGA in such a case. And with the advent of Oracle12c the overhead of using a gtt is further reduced with the new temporary-undo feature, which causes no redo to be generated for undo of temporary tables (see the temp_undo_enabled spfile parameter).


Now given our earlier attempts to code validation logic for the example constraint (All managers must have a clerk in the same department), we really only need to convey the number of the department for which we need to validate the constraint. And, we only need to convey this when a manager is indeed inserted. However we anticipate more than just (this) one multi-row constraint in which the EMP table is involved. And since we do not want to create a gtt per constraint, but rather one per (application) table, it is necessary to insert the values of all involved columns of the multi-row constraint. And to insert them always. I.e. not just when a manager is inserted, for we can have another multi-row constraint on the EMP table that is interested in other job values (not just managers). We then move the logic of when and for which department the constraint needs to be validated to the statement trigger. This also has makes the code more elegant: all validation logic for the constraint is located in one piece of code.

Here is the validation logic sofar for our constraint then.


Note that all the row-trigger does, is keep track of the relevant column values that are being inserted. The real validation logic for our constraint sits in the statement-trigger. For every department into which a manager just got inserted (we conveniently query our gtt for this) , it calls out to the (earlier created) p_check_for_clerk procedure. A few remarks on this implementation:

  • We query distinct deptno's from the gtt, to prevent checking a department more than once, in case an insert statement just inserted more than one manager into a department.
  • The query that access the EMP table inside the p_check_for_clerk procedure, will not hit the mutating table anymore, as at 'after statement' time the EMP table isn't in a mutating state anymore.
  • That query will also be immune for any order into which a multi-row insert statement will have inserted new EMP rows and it will see these rows: i.e. the issues of the previous two 'workarounds' (autonomous TX and loopback db-link) do not exist now.

Here's a test of this solution, using the same insert from the EMP_STAGE table we used earlier.


So, we now have a trigger based solution to enforce our constraint. But we are of course not done yet. We have only tackled the inserting of managers now. There are however other DML-statements that can potentially violate this constraint, and for which we will need to develop (more) triggers. Can you guess them?

And there is more after that... In a future post we will also discuss the need to serialize multiple transactions for correct enforcement of constraints.

Thanks for tuning in again.

51 comments:

  1. ฉันโชคดีมากที่ได้ดูบทความนี้ หวังว่าคุณจะมีความสุขและมีความสุขเสมอ

    giảo cổ lam giảm cân

    giảo cổ lam giảm béo

    giảo cổ lam giá bao nhiêu

    giảo cổ lam ở đâu tốt nhất

    ReplyDelete
  2. I'm cheerful I found this blog! Every now and then, understudies need to psychological the keys of beneficial artistic articles forming. Your top notch information about this great post can turn into a legitimate reason for such individuals. decent one


    AI Course

    ReplyDelete
  3. You should talk it's shocking. Your blog survey would extend your visitors. I was fulfilled to find this site.I expected to thank you for this phenomenal read!!
    hrdf scheme

    ReplyDelete
  4. It is the expect to give noteworthy information and best takes a shot at, including a perception of the regulatory cycle.
    https://360digitmg.com/course/data-analytics-using-python-r

    ReplyDelete
  5. This is a great motivational article. In fact, I am happy with your good work. They publish very supportive data, really. Continue. Continue blogging. Hope you explore your next post

    data science training

    ReplyDelete
  6. I feel extremely glad to have seen your site page and anticipate such a large number of additionally engaging occasions perusing here. Much obliged again for all the subtleties.
    HRDF training

    ReplyDelete
  7. Thank you so much for this intresting blog. Visit Ogen Infosystem for Website and SEO Services in Delhi, India. For more information visit our website.
    Web Designing Company in Delhi

    ReplyDelete
  8. Nice & Informative Blog !
    Directly place a call at our QuickBooks Customer Service Number 1-(855) 550-7546, for instant help.Our experts are well-trained & highly-qualified technicians having years of experience in handling user’s complexities.

    ReplyDelete
  9. After reading your article I was amazed. I know that you explain it very well. And I hope that other readers will also experience how I feel after reading your article.
    data science training

    ReplyDelete
  10. I want to say thanks to you. I have bookmark your site for future updates.
    data scientist certification malaysia

    ReplyDelete
  11. I am searching for and I love to post a remark that "The substance of your post is wonderful" Great work!
    data science training in malaysia

    ReplyDelete
  12. Hey! Excellent work. Being a QuickBooks user, if you are struggling with any issue, then dial QuickBooks Phone Number Our team at QuickBooks will provide you with the best technical solutions for QuickBooks problems.

    ReplyDelete


  13. Hey friend, it is very well written article, thank you for the valuable and useful information you provide in this post. Keep up the good work! FYI, please check these depression, stress and anxiety related articles.
    How to Build a Portfolio with ETFs, My vision for India in 2047 postcard, Essay on Unsung Heroes of Freedom Struggle

    ReplyDelete
  14. Are you looking for the best web design company in navi mumbai? If yes then connect with CSS Founder that provides always best website design & development services at an affordable price.

    ReplyDelete
  15. This is nice and informative post that you shared. I like to reading your all blogs. Thanks for posting this amazing blog.
    luxury projects in noida extension

    ReplyDelete
  16. If you want to get the best tent supplier in Dubai, UAE then you must go with AL Mumtaz Tents. We can be the right choice for you as we can provide you all types of tents at an affordable price. Tent Supplier in UAE

    ReplyDelete
  17. I appericiate your blog iPhone Insurance is useful thanks for sharing such a good and informative information

    ReplyDelete
  18. Really useful information. Thank you so much for sharing.It
    Website designing company in Mumbai

    ReplyDelete
  19. I will truly value the essayist's decision for picking this magnificent article fitting to my matter.Here is profound depiction about the article matter which helped me more.

    ReplyDelete
  20. 360DigiTMG, the top-rated organisation among the most prestigious industries around the world, is an educational destination for those looking to pursue their dreams around the globe. The company is changing careers of many people through constant improvement, 360DigiTMG provides an outstanding learning experience and distinguishes itself from the pack. 360DigiTMG is a prominent global presence by offering world-class training. Its main office is in India and subsidiaries across Malaysia, USA, East Asia, Australia, Uk, Netherlands, and the Middle East

    ReplyDelete
  21. 360DigiTMG, the top-rated organisation among the most prestigious industries around the world, is an educational destination for those looking to pursue their dreams around the globe. The company is changing careers of many people through constant improvement, 360DigiTMG provides an outstanding learning experience and distinguishes itself from the pack. 360DigiTMG is a prominent global presence by offering world-class training. Its main office is in India and subsidiaries across Malaysia, USA, East Asia, Australia, Uk, Netherlands, and the Middle East.

    ReplyDelete
  22. Are you looking for the best SEO Company in andheri that can increase your online business worldwide? If yes then connect with PromoteDial.

    ReplyDelete
  23. The main function of a website is to make your business accessible to people easily and provide information about your business, with a website, you can also expand your customers. With a website, you can be miles ahead of your competitors. Contact us if you want to build a website
    Web design company Nashvile

    ReplyDelete
  24. Parenting Tips When you have to confront your child, avoid blaming, criticizing, or fault-finding, which undermine self-esteem and can lead to resentment. Instead, strive to nurture and encourage, even when disciplining your kids. Make sure they know that although you want and expect better next time, your love is there no matter what. 9 Things My Nine Year Old Has Taught Me, Common Children’s Allergies: Dust, Parents’ Guide to Pretending to Listen to Their Kids, 4 Modern Parenting Tips For This New Age, Why is My Teenager So Angry?, What Every Parent Needs to Know About Child Development and the Backslide, How to Talk to Your Teenager About Sex, Your Baby: Making a Smooth Transition from Womb to World and Dog Safety Tips for Parents with Young Children

    ReplyDelete
  25. Valuable Information. I hope you will upload these types of articles in the future as well.
    Patient Care Services in Greater Noida

    ReplyDelete
  26. This is a great post. I like this topic.This site has lots of advantage.I found many interesting things from this site. It helps me in many ways.Thanks for posting this again.
    eniac full form in computer
    dvd full form
    sit full form
    pcc full form
    iucn full form
    full form of lcd
    brics full form
    tally erp full form
    full form of ctbt
    crpf full form

    ReplyDelete
  27. CSS Founder is a website design and SEO company that has won the hearts of thousands of people with its work, and website design and SEO Is providing very good service in the field.
    Website designing company in allahabad

    ReplyDelete
  28. Our digital marketing and website design services company, the name is CSS Founder, which provides the very best services there is no better than CSS Founder, which has been providing services in the field of digital marketing and website design services for the last 20 years and CSS Founder has made the business of people through the website and digital marketing. Click the link and join us. Website design company Giza egypt

    ReplyDelete
  29. Wow, cool post. I'd like to write like this too - taking time and real hard work to make a great article... but I put things off too much and never seem to get started. Thanks though Website design company in Salem

    ReplyDelete
  30. Thanks for the valuable information. “Good SEO is paying attention to all the details that most bloggers ignore.” Seo Company in Detroit

    ReplyDelete
  31. “We wander for distraction, but we travel for fulfilment.” Turkey visa apply online

    ReplyDelete
  32. theteamology is the best Best PR Agency in Bardhaman. For mor details please contact us!

    ReplyDelete
  33. If you are looking for the most trusted and efficient digital marketing agency then The Marcom Avenue is your best choice. They offer end-to-end assistance from managing social media to creating a website and delivering the SEO requirement.

    ReplyDelete

  34. LoadRunner supports different technologies, programming tools, and communication protocols. No product on the market supports as many protocols for Performance Testing as this one. The outcomes of the LoadRunner software's performance tests are contrasted with those of other tools. To learn more about loadrunner, join the Loadrunner course in Chennai at FITA Academy.
    Loadrunner course in Chennai

    ReplyDelete
  35. Thanks for providing valuable information, keep posting more Spoken English Training In Pune

    ReplyDelete
  36. Do you want to know the TIPS TO MAKE YOUR WEBSITE ATTRACTIVE? If yes then read this blog for getting more information.

    ReplyDelete
  37. If you have more questions about the Saudi Arabia Visa, contact our customer support team at any time via online and visit Our site Visitsaudiarabiavisa

    Saudi arabia visa apply online

    ReplyDelete
  38. According to EPR guidelines for plastic packaging, Producers, Importers, Brand-Owners, EPR Registration For Plastic Waste and Plastic Waste processors shall have to register through the centralized online EPR portal developed by the Central Pollution Control Board (CPCB). PIBOs shall have to obtain EPR Registration for Plastic Waste Management from CPCB.

    ReplyDelete
  39. If you are planning to visit India, applying for a visa online is a convenient and efficient option. With our company, Visits Visa, you can easily apply for an India Visa online. Our streamlined process ensures a hassle-free application experience while saving you time and effort. Visit our website today to seamlessly begin your India visa application process.

    ReplyDelete
  40. Fulfill your wish to visit India with your family and friends with the help of India Visa Apply. We provide you with hassle-free Indian Visa services to make you feel comfortable and energetic on your journey.

    ReplyDelete
  41. Choosing the right website design company is vital to the success of your online business. A well-designed website is not only aesthetically pleasing but also plays an important role in attracting and retaining customers. With so many options available, choosing the right website design company can be overwhelming. That's where CSS Founders comes in. As a top and trusted website designing company in Mumbai, CSS Founder has a proven track record of providing high-quality and innovative website designs.

    ReplyDelete