Tuesday, November 22, 2011

So what triggers are we talking about?

Here's an overview of the talk.


This post will deal with the first bullet: the scope, what triggers are we talking about? And what triggers are we not talking about. Then there will probably be a few posts on 'properties' of the triggers, most notably I will spend some time on explaining the infamous mutating table error. Next we move on to a high level classification of use-cases of triggers. And talk a bit about why some of these might be considered harmful. Finally we will explain, in detail, the one use case where triggers are the perfect means to achieve the end.


The most common types of triggers, the ones everybody probably used at some time in their pl/sql programming career, are the "DML event" triggers. As above slide shows, there are twelve of such triggers: four each for every type of DML statement, Insert, Update and Delete. These triggers will be fired by the DBMS before a DML statement, after a DML statement, and before/after each affected row of the triggering DML statement. Stuff you all know right? The big difference between the statement-level and the row-level triggers, is that the latter ones can inspect (and change) the column-values of the current row that is being inserted/deleted/updated.


So here's an example. Suppose we have an EMP table that holds employees, and we want to execute an update statement that will increase the salary of all clerks (see update statement above). This will for the given table affect three rows. The before update statement trigger will then fire once. Next for each affected row the before and after row triggers will fire. And finally the after statement trigger will fire.

So if we create the four update triggers on the EMP table as follows:


We will get the following output (given we have set serveroutput to on).


Nothing new so far, I hope. Before we continue I just wanted to mention that as of Oracle11G we have the compound trigger feature.


 A compound trigger enables us to create the four update triggers above all in one go as follows:


Now, do you know why Oracle introduced compound triggers? I'll talk about that in a later post. What I'll say now is this: compound triggers are the answer of an enhancement request made by you (the pl/sql community) a long time ago. Because you have always hit a certain programming pattern with regards to triggers, when using them for a certain use case. Again I'll explain this in more detail in a future post.

So these are the triggers that are in-scope of this blog: DML event triggers, be them created individually or four in one go using the compound trigger mechanism.

Oracle DBMS offers us with many more triggers:


All of which will not be the matter of subject for this blog.

Stay tuned.


21 comments:

  1. Nice to see you got time to start this blog. Your presentation is most certainly a reason to attend a conference.

    The avoiding mutating table issue is most certainly something an Oracle programmer shoud have education about. At least until Oracle introduces exclusion constraints that is available in PostgreSQL. Looking forward for your future posts.

    ReplyDelete
  2. Hei Rafu,

    Yes I have read about Jeff Davis' exclusion constraints in Postgres. But still they only solve a class of constraints.
    What we really need is CREATE ASSERTION from the Ansi/Iso SQL standard...

    But I'm getting ahead of things now.

    Toon

    ReplyDelete
  3. I applaud the publication of your article on triggers. It's a good reminder to look on the DevOps training.

    It is recommended to take DevOps training in Chennai quora

    Thank you for sharing with us the post that you have worked so hard to refine.


    DevOps training in chennai with placement | Best DevOps training in chennai | DevOps training in chennai OMR | DevOps training in chennai Velachery |DevOps training in chennai

    ReplyDelete
  4. It is a great post. Keep sharing such kind of useful information.

    sustainable-hyderabad
    Guest posting sites

    ReplyDelete
  5. Infycle Technologies offers the Best Data training in chennai and is widely known for its excellence in giving the best Data Science Certification course in Chennai. Providing quality software programming training with 100% placement & to build a solid career for every young professional in the software industry is the ultimate aim of Infycle Technologies. Apart from all, the students love the 100% practical training,
    which is the specialty of Infycle Technologies. To proceed with your career with a solid base, reach Infycle Technologies through 7502633633.

    ReplyDelete
  6. Infycle Technologies, the
    No.1 software training institute in Chennai
    offers the leading Python course in Chennai for tech professionals and students at the best offers. In addition to the Python course, other in-demand courses such as Data Science, Selenium, Oracle, Java, Power BI, Digital Marketing also will be trained with 100% practical classes. After the completion of training, the trainees will be sent for placement interviews in the top MNC's. Call 7504633633 to get more info and a free demo.

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete
  8. Very Informative blog thank you for sharing. Keep sharing.

    Best software training institute in Chennai. Make your career development the best by learning software courses.

    devops training in chennai
    azure training in chennai
    power bi training in chennai

    ReplyDelete
  9. I believe there are many more pleasurable opportunities ahead for
    individuals that looked at your site.
    I believe there are many more pleasurable opportunities ahead for
    individuals that looked at your site.

    ReplyDelete

  10. Quick up for the best offer of AWS DevOps Training in Chennai from Infycle Technologies, Excellent software training in Chennai. A massive place to learn other technical courses like Power BI, Cyber Security, Graphic Design and Animation, Block Security, Java, Oracle, Python, Big data, Azure, Python, Manual and Automation Testing, DevOps, Medical Coding etc., with outstanding training with experienced trainers with a fresh environment with 100+ Live Practical Sessions and Real-Time scenario after the finalisation of the course the trainee will able to get through the interview in top MNC’s with an amazing package for more enquiry approach us on 7504633633, 7502633633.

    ReplyDelete
  11. GREAT POST, THANKS FOR SHARING VALUABLE INFORMATION, KEEP POSTING Software Testing Classes in Pune

    ReplyDelete