tag:blogger.com,1999:blog-13399934485940090522024-03-17T20:04:20.779-07:00Triggers Considered Harmful, Considered HarmfulWithin the Oracle community, there is a general consensus that database triggers are to be considered harmful.
I find this general consensus harmful...Toon Koppelaarshttp://www.blogger.com/profile/08153913435188725112noreply@blogger.comBlogger15125tag:blogger.com,1999:blog-1339993448594009052.post-2944981029592410112015-01-04T07:39:00.003-08:002015-01-04T07:39:37.422-08:00Continuing the story<div>
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.<br />
<br />
So here goes.</div>
<div>
<br />
Let's first recap what we did sofar:<br />
<ul>
<li>In <a href="http://harmfultriggers.blogspot.nl/2011_11_01_archive.html" target="_blank">november 2011</a> 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.</li>
<li>Then in <a href="http://harmfultriggers.blogspot.nl/2011_12_01_archive.html" target="_blank">december 2011 </a>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.</li>
<li>The <a href="http://harmfultriggers.blogspot.nl/2012_01_01_archive.html" target="_blank">month thereafter</a> I demonstrated that using a statement trigger (instead of a row trigger) as a workaround has a serious performance disadvantage.</li>
<li>I then took a step back and <a href="http://harmfultriggers.blogspot.nl/2012_02_01_archive.html" target="_blank">discussed various other use-cases</a> for DML-triggers. For these other use-cases I agree with the common consensus that triggers are to be considered harmful.</li>
<li><a href="http://harmfultriggers.blogspot.nl/2012_03_01_archive.html" target="_blank">Here</a> 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.</li>
<li>Next I discussed the broader topic of constraints, and how they can be <a href="http://harmfultriggers.blogspot.nl/2013/03/data-integrity-constraint-classification.html" target="_blank">meaningfully classified</a>: multi-row constraints being one of the classes.</li>
<li>I showed how two other constraint classes (<a href="http://harmfultriggers.blogspot.nl/2013/03/implementing-attribute-and-tuple.html" target="_blank">attribute and tuple constraints</a>) ideally should be implemented using a declarative CHECK, and not via a trigger.</li>
<li>In the following post I discussed <a href="http://harmfultriggers.blogspot.nl/2013/03/and-what-about-table-constraints.html" target="_blank">different implementation strategies</a> 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.</li>
<li>I then discussed the materialized view strategy in some detail <a href="http://harmfultriggers.blogspot.nl/2013/03/the-materialized-view-approach-for.html" target="_blank">in this post</a>.</li>
</ul>
<div>
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:</div>
<ul>
<li>Workaround 1a: a row-trigger where we wrapped the query accessing the mutating table in an autonomous transaction (thereby preventing ORA-04091).</li>
<li>Workaround1b: a row-trigger where we queried the mutating table via a loopback database-link, which also prevents the error.</li>
<li>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).</li>
</ul>
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.<br />
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjCI4J3qGePg_L_4qwLnCsuO6W5UGOOR6hMbl6ap7xvksrgALhMWsduYkVdSyMJmvcyUDlp5YoGN0K7C3yAP6Xw6WdPWjxWr9rx0o4xiovC7qeL9yFXzLBvea_zR8ac0EImNBw5mqCtLVBo/s1600/p48.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjCI4J3qGePg_L_4qwLnCsuO6W5UGOOR6hMbl6ap7xvksrgALhMWsduYkVdSyMJmvcyUDlp5YoGN0K7C3yAP6Xw6WdPWjxWr9rx0o4xiovC7qeL9yFXzLBvea_zR8ac0EImNBw5mqCtLVBo/s1600/p48.JPG" height="297" width="400" /></a></div>
<div>
<br /></div>
We can employ three 'container'-types to convey this information:<br />
<ol>
<li>Use a persistent packaged (table) variable</li>
<li>Use compound triggers and its state variables</li>
<li>Use a global temporary table (gtt)</li>
</ol>
<div>
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.</div>
<br />
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 <a href="https://docs.oracle.com/database/121/REFRN/refrn10326.htm#REFRN10326" target="_blank">temp_undo_enabled</a> spfile parameter).<br />
<br /></div>
<div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3qakuuY0NyMJeYlLeADqTzzX8lMgKjNttPErmLwu-S9ZWJeJBYvul3xy4ItW95baKQXa44uf_LBTa1J8aXTm5vRSu4Wq2vLrqjxia8O5i7wIJGD1dWCiJIRkCUkketQOCcfJa-VhywwLT/s1600/p49.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3qakuuY0NyMJeYlLeADqTzzX8lMgKjNttPErmLwu-S9ZWJeJBYvul3xy4ItW95baKQXa44uf_LBTa1J8aXTm5vRSu4Wq2vLrqjxia8O5i7wIJGD1dWCiJIRkCUkketQOCcfJa-VhywwLT/s1600/p49.JPG" height="297" width="400" /></a></div>
<br />
Now given our <a href="http://harmfultriggers.blogspot.nl/2011/11/workarounds-for-ora-04091.html" target="_blank">earlier attempts</a> 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 <i>all involved columns</i> 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.<br />
<br />
Here is the validation logic sofar for our constraint then.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhG0GTlDZthmr4L6c3vSlg3boO3CzUyrrxqq_qFq7QetJFwB0Si9cqRG_11gzcjH7XwVAqLdDDkj25yfOrP_-IcoaCs9le33dcJfW9HvPc9xOl6UmPNbdr_mwvl2RIbFZoQfvzML7fYVyo5/s1600/p50.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhG0GTlDZthmr4L6c3vSlg3boO3CzUyrrxqq_qFq7QetJFwB0Si9cqRG_11gzcjH7XwVAqLdDDkj25yfOrP_-IcoaCs9le33dcJfW9HvPc9xOl6UmPNbdr_mwvl2RIbFZoQfvzML7fYVyo5/s1600/p50.JPG" height="297" width="400" /></a></div>
<br />
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:<br />
<br />
<ul>
<li>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.</li>
<li>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.</li>
<li>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.</li>
</ul>
<br />
Here's a test of this solution, using the same insert from the EMP_STAGE table we <a href="http://harmfultriggers.blogspot.nl/2011/12/mutating-table-error-prevents-non.html" target="_blank">used earlier</a>.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZrAGlNdrnJMDfI58NPbqnqRxizOGxibagIZ81RNsvB-_pn8IJ-yhgHQtPTsogm5x4chxcNJXHJTj7YwQY-awWnp9TxWS6U6S6s-dZcoAYAoqFYos-8qbvtU8E8vBo-VoK3QBiUMu41A4m/s1600/p51.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZrAGlNdrnJMDfI58NPbqnqRxizOGxibagIZ81RNsvB-_pn8IJ-yhgHQtPTsogm5x4chxcNJXHJTj7YwQY-awWnp9TxWS6U6S6s-dZcoAYAoqFYos-8qbvtU8E8vBo-VoK3QBiUMu41A4m/s1600/p51.JPG" height="296" width="400" /></a></div>
<br />
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?<br />
<br />
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.<br />
<br />
Thanks for tuning in again.</div>
Toon Koppelaarshttp://www.blogger.com/profile/08153913435188725112noreply@blogger.com51tag:blogger.com,1999:blog-1339993448594009052.post-62669620691457151122013-03-08T04:32:00.000-08:002013-03-08T04:32:55.426-08:00The materialized view approach for implementing a table constraintIn yesterdays post I announced that I'd spent a separate post on how we can use materialized views to enforce table constraints. So here goes.<br />
<br />
The high-level cookbook for this approach is as follows:<br />
<ol>
<li>We create a materialized view that refreshes on commit,</li>
<li>The materialized view is defined in such a manner that it will hold no rows when the table constraint is adhered to by the current transaction trying to commit,</li>
<li>And it is defined such that it will hold (at least) one row when the table constraint is violated by the current transaction trying to commit,</li>
<li>We devise a construct such that on-commit refresh of the materialized view *always* fails whenever (at least) one row is materialized in the view. This can be done in two manners:<br />1) we add a check constraint on the underlying table of the materialized view that always fails, or<br />2) we add a before-insert row-trigger on the underlying table of the materialized view that always fails.</li>
</ol>
<div>
Here's the slide on this from my 'harmful triggers' presentation:</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgO4R0p4b7ChVy24izfN19g6Cgdej26-RsF7a2d09-UOuiMb2WL30dkoCpSPPplDyBEwdyC8pMhiVQXB-7fUywmu1p94yFZtqgRykOAN7LzO9GqwIVuKcvHGUXm8JRJ9iPTd84gpmgqPJoo/s1600/x1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="298" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgO4R0p4b7ChVy24izfN19g6Cgdej26-RsF7a2d09-UOuiMb2WL30dkoCpSPPplDyBEwdyC8pMhiVQXB-7fUywmu1p94yFZtqgRykOAN7LzO9GqwIVuKcvHGUXm8JRJ9iPTd84gpmgqPJoo/s400/x1.jpg" width="400" /></a></div>
<div>
<br /></div>
<div>
So let's try this with our example constraint (managers require a clerk in same department). The assertion for this constraint was:</div>
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>not exists
(select 'a department'
from (select distinct deptno from emp) d
where exists
(select 'a manager in d'
from emp e
where e.deptno = d.deptno and e.job = 'MANAGER')
and not exists
(select 'a clerk in d'
from emp e
where e.deptno = d.deptno and e.job = 'CLERK')
)</code></pre>
<div>
<br /></div>
<div>
With this assertion we can now mechanically generate a materialized view for our constraint, using the DUAL table. Note: we negate the assertion so that the materialized view ends up having characteristics 2 and 3 from our cookbook above. So the 'not exists' turns into an 'exists'.<br />
<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>create materialized view managers_need_clerk
refresh fast on commit
as
select 'wrong' as text
from dual
where exists
(select 'a department'
from (select distinct deptno from emp) d
where exists
(select 'a manager in d'
from emp e
where e.deptno = d.deptno and e.job = 'MANAGER')
and not exists
(select 'a clerk in d'
from emp e
where e.deptno = d.deptno and e.job = 'CLERK')
)
/</code></pre>
<br /></div>
Note: we explicitly want this materialized view to be "fast refreshable", meaning that Oracle will use intelligence to minimize the work required to refresh this view. In order for Oracle to be able to do so, we would also need to create a materialized view log on the table involved, which is EMP (and DUAL?) in this case. Creating the MV-log is left for the reader.<br />
<br />
And finally we add a CHECK clause to the underlying table segment of this materialized view (whose name is the same as the materialized view). This CHECK clause is such that it always evaluates to FALSE.<br />
<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>alter table managers_need_clerk add CHECK( 0 = 1 )
/</code></pre>
<br />
The way this now should work is that whenever a transaction introduces a department that has a manager, but no clerk, and tries to commit, this on-commit refresh materialized view will produce a single row to be inserted into the underlying table segment. This triggers validation of our CHECK clause, which will always fail. Which in turn causes the commit to fail, thereby preventing this transaction to successfully complete.<br />
<br />
All seems very well, until you now hit the following error:<br />
<br />
<b>ORA-12052: cannot fast refresh materialized view [owner].[mat.view]</b><br />
<br />
There are still various restrictions imposed upon materialized views for them to be fast refreshable. See the Oracle documentation for this. Sometimes you might be surprised though that a bit of rewriting a materialized view could end up such that the materialized view becomes fast refreshable. Rewriting them into straight joins is a strategy that might work here. For instance our materialized view above can be rewritten into this:<br />
<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>create materialized view managers_need_clerk
refresh fast on commit
as
select 'wrong' as text
from (select c.job
from emp m
,emp c
where m.job = 'MANAGER'
and m.deptno = c.deptno (+)
and 'CLERK' = c.job (+))
where job is NULL
/</code></pre>
<br />
I haven't tested above alternative: with the appropriate materialized view logs, it could well be fast refreshable now...<br />
<br />
On final comment on this approach for implementing table constraints: Oracle (must and) will serialize refreshes of the materialized view among simultaneously executing (or rather, committing) transactions. Rob van Wijk has a nice blogpost on this <a href="http://rwijk.blogspot.com/2010/01/enq-ji-contention.html" target="_blank">here</a>.<br />
<br />Toon Koppelaarshttp://www.blogger.com/profile/08153913435188725112noreply@blogger.com157tag:blogger.com,1999:blog-1339993448594009052.post-6598015551557852602013-03-07T05:21:00.001-08:002014-11-16T07:36:23.035-08:00And what about table constraints?In a<a href="http://harmfultriggers.blogspot.com/2013/03/data-integrity-constraint-classification.html" target="_blank"> previous post</a> we've introduced a classification scheme for constraints:<br />
<ul>
<li>attribute constraints</li>
<li>tuple constraints</li>
<li>table constraints</li>
<li>database constraints</li>
<li>dynamic constraints</li>
</ul>
<div>
And talked a bit about how we could implement the first two classes <a href="http://harmfultriggers.blogspot.com/2013/03/implementing-attribute-and-tuple.html" target="_blank">here</a>. In today's post we will make a start talking about how we can implement table constraints using triggers. But before we do that we will offer some thoughts on how the ideal world with regards to this subject would look like.</div>
<div>
<br /></div>
<div>
Long ago, in a galaxy far away, an ANSI/ISO SQL standardization committee came up with the ground breaking concept of a SQL ASSERTION. SQL assertions would make our lives real easy when it comes down to implementing table constraints. The example constraint "we cannot have a manager without a clerk in the same department" could be implemented as:<br />
<br /></div>
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>create assertion managers_need_clerk as
check(not exists
(select 'a department'
from (select distinct deptno from emp) d
where exists
(select 'a manager in d'
from emp e
where e.deptno = d.deptno and e.job = 'MANAGER')
and not exists
(select 'a clerk in d'
from emp e
where e.deptno = d.deptno and e.job = 'CLERK')
)
)
/
</code></pre>
<br />
Presto. Done.<br />
It would then be up to the DBMS to maintain this constraint. Of course we require the DBMS to do that<i> in an efficient manner</i>. This will all become clear during the course of the next couple of posts.<br />
<br />
By the way you may think, why not just do this with a CHECK constraint?<br />
<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>alter table emp add constraint managers_need_clerk as
check(not exists
(select 'a department'
from (select distinct deptno from emp) d
where exists
(select 'a manager in d'
from emp e
where e.deptno = d.deptno and e.job = 'MANAGER')
and not exists
(select 'a clerk in d'
from emp e
where e.deptno = d.deptno and e.job = 'CLERK')
)
)
/
</code></pre>
<br />
Well that's because CHECK constraints do not allow sub-queries. They don't allow that for the same reason as why we still do not have support for assertions. Both require the DBMS vendor to produce some seriously complex piece of software that can accept an arbitrary complex boolean SQL-expression and compute from that the most efficient way to maintain that boolean expression inside all concurrently running transactions. The<i> research and development effort</i> for this still needs to be done.<br />
<br />
<br />
So, we have to resort to some other means when it comes to implementing table constraints. Here's a list of possible implementation strategies.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgS_V3pOGSkAlem0naohkKxyrTgL3sWV4AwjbiJhgOUq7krKpNiROBuAHFQbBbTY8z8fAvW1NlD-1YAWuAhqqFGh8Bwpmx-2K2drZJU21jcvqo5eoi_9vIICJUSl993DLMkidFDuKMvnSGm/s1600/p1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgS_V3pOGSkAlem0naohkKxyrTgL3sWV4AwjbiJhgOUq7krKpNiROBuAHFQbBbTY8z8fAvW1NlD-1YAWuAhqqFGh8Bwpmx-2K2drZJU21jcvqo5eoi_9vIICJUSl993DLMkidFDuKMvnSGm/s400/p1.jpg" height="297" width="400" /></a></div>
<br />
So we've discussed the first one already: it's highly preferred, but unfortunately only a very partial solution. The only table constraints that we can deal with declaratively are:<br />
<br />
<ul>
<li>Keys (be them primary or unique), and</li>
<li>Foreign keys, in case the FK refers back to another column<i> in the same table</i> (in which case the foreign key is a table constraint, and not a database constraint).</li>
</ul>
<div>
The<b> trigger approach</b> is what the rest of this blog will be all about. Every table constraint can be implemented using triggers. Contrary to popular belief this is doable. But it *is* rather complex. The fact that this is a full solution for the table constraint class, is a big pro though.</div>
<div>
<br /></div>
<div>
Then there is the <b>API-approach</b>. This is the approach where you encapsulate all DML statements inside stored procedures, and disallow any direct DML access to your tables. The only way to modify your tables is through the stored procedure API-layer. And inside this API-layer, you deal with constraint validation, just before or right after you issue the DML statements. To me this is a fundamentally flawed solution, since it will always lead to constraint enforcing code duplication. And since it is not a "once and for all" solution. Every time you maintain your application and need to introduce new transactions, you'll have to take care of constraint enforcement again. Many people also tend to completely disregard the complexities involved. To a certain extent, they are the exact same complexities as are involved in the trigger approach: you'll have to take care of serialization and efficiency (all explained in future posts) in this approach too. And finally, in practice it is very difficult to maintain the enforcement of only allowing access to you tables via the API-layer. At some point people will find ways to go around the API-layer, and thereby be able to introduce corrupt data.</div>
<div>
<br /></div>
<div>
<br />
<div>
And there is the <b>function based index trick</b>. This is where we can use unique, function-based, indexes to implement keys across a subset of rows in a table. This too is a very partial solution for implementing table constraints. I'm assuming, since you read this blog, you are familiar with this trick, otherwise let me know in a comment and I'll provide you with an example.</div>
<br />
<br /></div>
<div>
Another approach is to employ materialized views in an ingenious manner. Like triggers, this is in theory a full solution, but in practice only a very partial one. It all boils down to the same<i> research and development effort</i> mentioned above when we discussed assertions, not having been done yet. We will discuss the <b>materialized view approach</b> in our next post.</div>
<div>
<br /></div>
<div>
Stay tuned.</div>
Toon Koppelaarshttp://www.blogger.com/profile/08153913435188725112noreply@blogger.com14tag:blogger.com,1999:blog-1339993448594009052.post-74747461500726105832013-03-03T08:30:00.001-08:002013-03-03T08:30:09.530-08:00Implementing attribute and tuple constraintsIn our <a href="http://harmfultriggers.blogspot.com/2013/03/data-integrity-constraint-classification.html" target="_blank">previous post</a> we have introduced a classification scheme for data integrity constraints. In todays post we will present thoughts & guidelines around how to implement the first two classes: <i>attribute</i> and <i>tuple</i> constraints.<br />
<br />
The examples given in the previous post were:<br />
<br />
<div style="background-color: white; color: #222222; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px; line-height: 18px;">
<b>Attribute constraints</b></div>
<div style="background-color: white; color: #222222; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px; line-height: 18px;">
<ul style="line-height: 1.4; margin: 0.5em 0px; padding: 0px 2.5em;">
<li style="margin: 0px 0px 0.25em; padding: 0px;">Salary must be a positive integer between 750 and 14000.</li>
<li style="margin: 0px 0px 0.25em; padding: 0px;">Job must be one of the following list: MANAGER, CLERK, SALESMAN, etc.</li>
</ul>
<div>
<b>Tuple constraints</b></div>
</div>
<div style="background-color: white; color: #222222; font-family: Arial, Tahoma, Helvetica, FreeSans, sans-serif; font-size: 13px; line-height: 18px;">
<ul style="line-height: 1.4; margin: 0.5em 0px; padding: 0px 2.5em;">
<li style="margin: 0px 0px 0.25em; padding: 0px;">A manager cannot have a salary less than 5000.</li>
<li style="margin: 0px 0px 0.25em; padding: 0px;">An employee working in department 10, cannot be a salesman.</li>
</ul>
</div>
<br />
We could implement these constraints using a trigger approach. Let's go down that road first since this blog is about triggers. We will have to figure out then which trigger types (we have 12 of them, <a href="http://harmfultriggers.blogspot.com/2011/11/so-what-triggers-are-we-talking-about.html" target="_blank">remember</a>) are best fit to implement these attribute and tuple level constraints?<br />
<ul>
<li>Row triggers or statement triggers?</li>
<li>Firing before or after the DML statement?</li>
<li>Firing when? On insert, update or delete?</li>
</ul>
<div>
<br />
A bit of thought quickly leads to:</div>
<div>
<ul>
<li>Row triggers, since they have the :old and :new variables that enable us to easily inspect the column value(s) involved in the constraint;</li>
<li>Before triggers seem obvious since we can prevent the rows actually being processed when they are in violation with one of these constraints.</li>
<li>And finally, the delete event is of no concern. We only require validation during insert and update. And even then *only* when columns involved in any of the constraints are affected.</li>
</ul>
<div>
<br />
Here is the trigger code that could implement the first attribute constraint example mentioned above. Not too difficult.</div>
</div>
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>create or replace trigger emp_aiur1
before insert or update on EMP
for each row
begin
--
if INSERTING or UPDATING('SALARY')
then
--
if :new.SALARY < 750 or :new.SALARY > 14000
then
--
raise_application_error(-20000,'Salary must be between 750 and 14000.');
--
end if;
--
end if;
end;
/
</code></pre>
<br />
Note that this isn't actually an example of our <a href="http://harmfultriggers.blogspot.com/2012/03/fourth-use-case-for-triggers.html" target="_blank">fourth use-case</a>: that use-case required triggers to execute queries. But as it so happens to be, using a trigger for implementing attribute (or tuple as we'll see hereafter) constraints doesn't require us to code any queries.<br />
<br />
Similarly we can code a trigger to implement a tuple constraint. Here's the trigger for the first tuple constraint example given above:<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>
create or replace trigger emp_aiur2
before insert or update on EMP
for each row
begin
--
if INSERTING or UPDATING('JOB') or UPDATING('SALARY')
then
--
if :new.JOB = 'MANAGER' and :new.SALARY < 5000
then
--
raise_application_error(-20000,'A manager cannot earn less than 5000.');
--
end if;
--
end if;
end;
/
</code></pre>
<br />
Note the numbering that was introduced in the trigger names above. Going down the road of using triggers to implement attribute and tuple constraints, also requires us to think about this. Do we combine all code for these constraints into one "after insert or update for each row" trigger per table? Seems plausible: for one we then control the order in which the validations occur. Yes, I'm aware of the FOLLOWS construct, we could use that also and keep the separate triggers. But it doesn't make the solution more elegant nor better maintainable, does it?<br />
<br />
Instead of the <i>procedural</i> approach above, a much, much better approach for implementing attribute and tuple constraints is of course to use the <i>declarative</i> CHECK syntax. Here's the equivalent for above two triggers:<br />
<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>alter table emp add constraint EMP_SAL1
check(SALARY between 750 and 14000)
/
alter table emp add constraint EMP_JOB_SAL1
check(JOB != 'MANAGER' or SALARY >=5000)
/
</code></pre>
<br />
The second CHECK clause might be difficult for you at first sight. That depends whether you know the important rewrite rule that we have in formal logic: how to rewrite an implication (IF...THEN...) into a disjunction (...OR...). Whenever a predicate A implies a predicate B, written as 'A implies B', then we can rewrite that as 'not A or B'. And the tuple constraint at hand is indeed an implication (as so many tuple rules are btw): <b>if</b> job is MANAGER <b>then</b> salary must be greater than or equal to 5000.<br />
<br />
The same question also pops up when using CHECK constraints: do we combine them all into one CHECK constraint (by logically AND-ing them all), or have separate CHECK constraints? My strong preference is to implement them as separate CHECK constraints. Granted you do not control the order in which they 'fire' in this case, but you do get better (ie. more detailed) errors.<br />
<br />
In our next post we will proceed to guidelines/thoughts around implementing multi-row (table and database) constraints.<br />
<br />
Thanks for tuning in.Toon Koppelaarshttp://www.blogger.com/profile/08153913435188725112noreply@blogger.com3tag:blogger.com,1999:blog-1339993448594009052.post-54154345670719371212013-03-03T03:26:00.001-08:002013-03-03T03:26:32.442-08:00Data integrity constraint classificationBefore we start investigating the complexities involved in implementing data integrity constraints using database triggers, we will first introduce a classification schema for data integrity constraints. Agreeing upon a classification scheme for constraints, helps us a lot in implementing them: for we can then approach the problem area on a class-by-class basis.<br />
<br />
A classification scheme needs to have a few properties:<br />
<ol>
<li>The constraint classes should be <i>mutually exclusive</i>: any given constraint should clearly fall into one, and only one, of the classes;</li>
<li>The scheme should <i>cover all types of constraints</i>: i.e. it cannot be that we can think of a constraint that doesn't fall into one of the defined classes;</li>
<li>It should <i>be practical</i>: and by this we mean it should help us when implementing constraints. Or, put in another way, the issues we have when implementing constraints of the same class should be similar for these constraints, and the issues we have for implementing constraints of a different class, should be different.</li>
</ol>
<div>
A commonly used classification scheme for constraints, that has all above properties, is one that takes the increasing scope that a constraint can have in a database design, as the driver for defining classes.<br />
<br />
<b>Class 1: Attribute constraints.</b><br />
These are constraints that can be specified (and thus checked) by referring to (or inspecting) only one column value of a tuple (ie. row).<br />
<br />
<b>Class 2: Tuple constraints.</b><br />
<br />
These are constraints that can be specified (and thus checked) by referring to (or inspecting), at least two column values of a tuple. This class can also be called 'multi attribute' constraints.<br />
<div>
<br /></div>
<div>
<b>Class 3: Table constraints.</b></div>
<div>
These are constraints that can be specified (and thus checked) by referring to (or inspecting), at least two tuples of a table. This class can also be called 'multi tuple' constraints.<br />
<br /></div>
<div>
<div>
<b>Class 4: Database constraints.</b></div>
<div>
These are constraints that can be specified (and thus checked) by referring to (or inspecting), at least two tables. This class can also be called 'multi table' constraints.</div>
</div>
<div>
<br /></div>
<div>
In case the above scheme is totally new for you, here are a few examples:</div>
<div>
<br /></div>
<div>
<b>Attribute constraints</b></div>
<div>
<ul>
<li>Salary must be a positive integer between 750 and 14000.</li>
<li>Job must be one of the following list: MANAGER, CLERK, SALESMAN, etc.</li>
</ul>
<div>
<b>Tuple constraints</b></div>
</div>
<div>
<ul>
<li>A manager cannot have a salary less than 5000.</li>
<li>An employee working in department 10, cannot be a salesman.</li>
</ul>
<div>
<b>Table constraints</b></div>
</div>
<div>
<ul>
<li>There cannot be two employees with the same employee number.</li>
<li>A manager must always be accompanied by a clerk in the same department (hmm, sounds familiar this one...)</li>
</ul>
<div>
<b>Database constraints</b></div>
</div>
<div>
<ul>
<li>Every employee must work in a known department.</li>
<li>No (non SF-based) department can have a higher salary budget then any San Francisco (SF) based department.</li>
</ul>
Did you spot the primary/unique key? And the foreign key?</div>
<br />
Just verify for yourself that this classification scheme covers all constraints. Think of a few constraints in your database design, and see in what classes they fall. It could be that you find a constraint that's not covered yet. That would be because above four classes jointly only cover <b>static constraints</b>. These are constraints that can be verified by looking at a database state (or snapshot) in rest. There is another class in itself for what are called <b>dynamic constraints</b>. Dynamic constraints, sometimes referred to as <i>transaction</i> or <i>database-state change</i> constraints, cannot be verified by looking at a database state at rest. That's because the specification of these constraints require the context of a transaction. Examples of dynamic constraints are:<br />
<br />
<ul>
<li>Cannot change the salary of an employee without the same transaction also writing a log-record in [some table] that details the user who performed the change and the time when this was done;</li>
<li>We register new hires at most one month before they join the company. Or, put in another way: the hiredate of an employee cannot be more than one month in the future;</li>
<li>(a classic one) Salary can only increase, never decrease.</li>
</ul>
<br />
The four static constraint classes plus the dynamic constraint class are,<br />
<br />
<ul>
<li>all mutually exclusive, and</li>
<li>together cover all possible constraints.</li>
</ul>
<br />
</div>
And as it so turns out this classification scheme is also a practical one. Similar issues arise when implementing constraints of the same class. We'll further investigate that in our next post, where we will start with implementation guidelines for constraints on a class-by-class basis.<br />
<br />
<br />Toon Koppelaarshttp://www.blogger.com/profile/08153913435188725112noreply@blogger.com7tag:blogger.com,1999:blog-1339993448594009052.post-32800442907196858902012-03-26T12:32:00.000-07:002012-03-26T12:32:17.620-07:00The fourth use-case for triggersIn <a href="http://harmfultriggers.blogspot.com/2012/02/where-tk-agrees-with-tk-or-why-are.html">our previous post</a> we talked about three of the four use-cases we introduced. Triggers can:<br />
<ol>
<li>Assign/modify (row) column values.</li>
<li>Execute insert/update/delete statements.</li>
<li>Execute non-transactional stuff.</li>
</ol>
<div>
We've also discussed why indeed using triggers for above use-cases, should be considered harmful. Their automagic side-effects are often not expected by other programmers / users of the database application.</div>
<br />
In this post we continue with use-case four. Triggers can:<br />
<ol start="4">
<li>Execute select statements.</li>
</ol>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEisABysLRGizgyTege_Br6Q4ngWJPmpDSYH4r2QyRDULtouwckYweQHE0gGGafAKml6dPzkrlpeS1H45oGI9G6G9BCFR9m_Sw9D0T8r5DG8KElnQbz-jMwZfOOakUT4vsv63onyvRSQiYU4/s1600/31_usecase4.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="297" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEisABysLRGizgyTege_Br6Q4ngWJPmpDSYH4r2QyRDULtouwckYweQHE0gGGafAKml6dPzkrlpeS1H45oGI9G6G9BCFR9m_Sw9D0T8r5DG8KElnQbz-jMwZfOOakUT4vsv63onyvRSQiYU4/s400/31_usecase4.jpg" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
This use-case, where we have the need to execute queries inside triggers, is a very common use-case, and it is one that always (re)surfaces, in almost every project. And there's a reason for this: a very important database-concept requires you to query other data from within triggers.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
This concept is called: data integrity constraints. And it's exactly this use-case where, in my opinion, triggers form the means to an end.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Now beware though, and we give you this warning beforehand: using triggers to implement data integrity constraints, is by far not easy. In fact it's extremely complex (as we will detail in future posts). But to me that is no reason to not use triggers. And this use-case will not suffer from the automagic side-effects the other three use-cases had. So I disagree here when Tom Kyte says that even for this use-case 'thou shall not use triggers'.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgjDk1iz20eK99nt1syfysOd-gkJBiczLyApVBOv6x-hY24_JrLsy3RULc27msfHWFZV06DNqRgFpwaiJDwme7BSDqyA3L6sYGqx6V1mlapterhwyGfSmRnriPNY_VOa2dVdK_0sXTXuvsk/s1600/32_usecase4.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="298" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgjDk1iz20eK99nt1syfysOd-gkJBiczLyApVBOv6x-hY24_JrLsy3RULc27msfHWFZV06DNqRgFpwaiJDwme7BSDqyA3L6sYGqx6V1mlapterhwyGfSmRnriPNY_VOa2dVdK_0sXTXuvsk/s400/32_usecase4.jpg" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Recall the <a href="http://harmfultriggers.blogspot.com/2011/11/workarounds-for-ora-04091.html">'business rule'</a> that we were trying to implement using triggers: <i>a department cannot employ a manager without a clerk in the same department</i>. That rule is in fact a data integrity constraint. Now, few people know of the documented feature in the SQL standard called: <i>assertions</i>. SQL assertions have been part of the SQL standard since 1992. See <a href="http://savage.net.au/SQL/sql-92.bnf.html">this link</a> for the syntax/grammar (it's the eighth 'key sql statement' from the top of the list). Had database vendors supplied us with support for the two-decades old SQL assertion feature, then we could have straightforwardly implemented the data integrity constraint with one CREATE ASSERTION statement:</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjcOVcD8CNjo3FGV-0nNMdtvg8XcAUhx5-gC71s2iBvKF3foS8QJpPSpsA8Bca98Jjwe3TL2hCA_Km-YsqIeXeYjnYCsvhb6mcCkoOzMhj_34hMAhzPT_x9HYVMdrnl9ceszKwXIafcMMp3/s1600/33_usecase4.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="298" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjcOVcD8CNjo3FGV-0nNMdtvg8XcAUhx5-gC71s2iBvKF3foS8QJpPSpsA8Bca98Jjwe3TL2hCA_Km-YsqIeXeYjnYCsvhb6mcCkoOzMhj_34hMAhzPT_x9HYVMdrnl9ceszKwXIafcMMp3/s400/33_usecase4.jpg" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
Translating the above assertion into somewhat awkward english, it goes like this: there cannot exist a department such that this department employs a manager, and such that this department does not employ a clerk.<br />
<br />
Once the assertion is created, it's up to the DBMS to enforce the integrity constraint in a correct and (hopefully) efficient manner. Just like you expect the DBMS to correctly and efficiently enforce a uniqueness constraint, or a foreign key. Conceptually these constraints are no different than assertions: it's just that these two represent (constraint) patterns that occur so frequently in every database design, that we've been provided with dedicated SQL language constructs to declare them. But theoretically the UNIQUE / PRIMARY KEY and FOREIGN KEY language constructs are redundant, when assertions are available: both can be rewritten using a CREATE ASSERTION statement.<br />
<br />
Would you use assertions, if Oracle provided them? I certainly would, and you would probably too. You are using the other declarative means to implement data integrity (check constraints, primary key, foreign key) right now too, aren't you?<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiih0Y63bh79hZJNcLiKG-5SApSl49R0COehrD0yivgXa2JDVHl6wExNDvAmk7u0c9xkQD5iCY1Egxkpp0xglHgXMb8dvcsQ-T_FeEd1C9cw5w-N9M9jK5ThNDoCTqIkxmOq6VPsceUY6y6/s1600/34_usecase4.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="301" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiih0Y63bh79hZJNcLiKG-5SApSl49R0COehrD0yivgXa2JDVHl6wExNDvAmk7u0c9xkQD5iCY1Egxkpp0xglHgXMb8dvcsQ-T_FeEd1C9cw5w-N9M9jK5ThNDoCTqIkxmOq6VPsceUY6y6/s400/34_usecase4.jpg" width="400" /></a></div>
<br />
Implementing data integrity constraints in a declarative manner, enables a kind of separation of concerns: you implement your integrity constraints once-and-forall while you create your tables. And then you build business logic on top of the database design without having to worry about validating integrity constraints: all you need to cater for is error-handling, in case your business logic tries to modify/store data in such a way that it violates one or more of the declared integrity constraints. If you want to read up on this way of implementing data-centric applications <a href="http://thehelsinkideclaration.blogspot.com/2009/03/start-of-this-blog.html">theHelsinkiDeclaration.blogspot.com</a> is a good starting point.<br />
<br />
By the way, there's a reason why DBMS vendors have yet to supply us with support for the CREATE ASSERTION statement:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhbzOjC0SR4lDwQw-uTfGCoIdsZeUcowq0k9PraHgu_RbMtpj7RkBPSZsQy6qa6jg5gxoo_RTmeZMOYCaYeM4iOg1PAoWmMLFO3NS7kPDKIIxJLNtEDfrmOx_9zqntOX9gqrSwFJdc-WRAb/s1600/35_usecase4.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="297" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhbzOjC0SR4lDwQw-uTfGCoIdsZeUcowq0k9PraHgu_RbMtpj7RkBPSZsQy6qa6jg5gxoo_RTmeZMOYCaYeM4iOg1PAoWmMLFO3NS7kPDKIIxJLNtEDfrmOx_9zqntOX9gqrSwFJdc-WRAb/s400/35_usecase4.jpg" width="400" /></a></div>
<br />
Developing a correct and efficient implementation for an arbitrary complex assertion (which is what we're asking for), is very hard. By 'correct' we mean, the implementation must properly deal with <i>concurrent</i> transactions manipulating data that is involved in the assertion. By 'efficient' we mean, the implementation must not just run the SQL-predicate that was asserted in the create assertion command, but it must be able to a) detect <i>when</i> a transaction might potentially violate the assertion, and then b) run a <i>minimal check</i> to ensure the continued validity of the assertion.<br />
<br />
As far as I know the problem areas described above, haven't been fully researched yet by the scientific community: for one I'm unable to find this research, and had it been researched fully, it would have been fairly easy for a database vendor like Oracle to 'stand on the shoulders' of those researchers and provide us support for create assertion.<br />
<br />
So there we are: the fourth use-case for triggers being implementing data integrity constraints.<br />
<br />
To be continued...<br />
<br />Toon Koppelaarshttp://www.blogger.com/profile/08153913435188725112noreply@blogger.com1tag:blogger.com,1999:blog-1339993448594009052.post-42111594264089475272012-02-06T11:27:00.000-08:002012-02-06T22:02:35.092-08:00Where TK agrees with TK (or: why are triggers harmful)Sofar we've explored playing around with a few triggers to implement a business rule. In this post I'd like to step back a bit and take a birds-eye view at the common use-cases that I see brought up for introducing triggers into the app-development picture.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgPvXgZXOKG-HhXDc2pFzJlR_vNUTZYeUb3cGoII7jCpUTllQci6NrXnPM_4cyaWRYOk6gp-ZZhg_Ae-KGl4cKNYZ_ZfgY4dx80AnDArCd_bP-22C7FtbIId8RTyjzm28PQnuv6S0Ecu2yu/s1600/30_cus1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgPvXgZXOKG-HhXDc2pFzJlR_vNUTZYeUb3cGoII7jCpUTllQci6NrXnPM_4cyaWRYOk6gp-ZZhg_Ae-KGl4cKNYZ_ZfgY4dx80AnDArCd_bP-22C7FtbIId8RTyjzm28PQnuv6S0Ecu2yu/s400/30_cus1.jpg" width="400" /></a></div>
<br />
The first use-case is: we use triggers <i>to assign or modify column values of mutating rows</i> (that is, rows currently being inserted, updated, or deleted). Here's a few typical examples of this use case that, I'm sure, must look familiar to you too.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiFM0XEQeVz8dbcJeS53z38OIW6xrLfffqwboPISrCqRXY23thkHcp1ZCypOMfiajpz76o9J-o50n8B-EhZKYGTNnQhtzy6LR10ppbyg9y1V0tT2huhKN1oD_ZZld27Z-omxV9GgXQtThR0/s1600/30_cus2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="298" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiFM0XEQeVz8dbcJeS53z38OIW6xrLfffqwboPISrCqRXY23thkHcp1ZCypOMfiajpz76o9J-o50n8B-EhZKYGTNnQhtzy6LR10ppbyg9y1V0tT2huhKN1oD_ZZld27Z-omxV9GgXQtThR0/s400/30_cus2.jpg" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
For this use-case we always use a row-level trigger (for each row), as this type of trigger allows us to manipulate :new.[column-name] variables. We can re-assign supplied (by the trigger DML-statement) column values, or initialize ones that were not supplied. So for instance the use enters all column-values but the Created and By_User columns, which are initialized by a row-trigger we wrote.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
The second use-case is: we use triggers <i>to execute insert/update/delete statements</i>. So we perform some DML against a table. This table has some trigger, in which we perform more DML.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdfpjYDOuvUc8VOf3BAX93QjOU33MTqF0Ox4qgJdmdE4Zy56nMZf76YGniyBfCQBuQ3Ws974dpBlj0hbg_cob9mRgrDDX06rh7RIBjjMLS04yDffHrc1MVoSiC-hT6scM9j_AfnhzPII0k/s1600/30_cus3.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="298" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdfpjYDOuvUc8VOf3BAX93QjOU33MTqF0Ox4qgJdmdE4Zy56nMZf76YGniyBfCQBuQ3Ws974dpBlj0hbg_cob9mRgrDDX06rh7RIBjjMLS04yDffHrc1MVoSiC-hT6scM9j_AfnhzPII0k/s400/30_cus3.jpg" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
Of course this can lead to a cascading effect: the DML that we perform from within our trigger body might be against a table that too has triggers. In which there might be more DML-statements performed, and so on and so forth. Typical examples of why we perform DML-statements inside triggers, are: we want to audit changes against the table, or we want to maintain redundant data stored elsewhere in the database design.<br />
<br />
The third use-case is: we use triggers <i>to execute non-transactional stuff</i>. And by "non-transactional" I mean stuff that, once executed inside our transaction, does not participate in a rollback, in case we (the code-path) decides to perform one.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg8DCTosg5So8yd9K-tAzAfJDFBt94msVqydcAzukwP2Rnoo9pG4L1cQtAQL1jeRJBfsCQzQF2gtlOYndpR-IZUDRrtI3-v8H6FD1-tXMHHY1Gs31AdjghcacBGRgr4ltuivh2Zi9V9rf9H/s1600/30_cus4.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg8DCTosg5So8yd9K-tAzAfJDFBt94msVqydcAzukwP2Rnoo9pG4L1cQtAQL1jeRJBfsCQzQF2gtlOYndpR-IZUDRrtI3-v8H6FD1-tXMHHY1Gs31AdjghcacBGRgr4ltuivh2Zi9V9rf9H/s400/30_cus4.jpg" width="400" /></a></div>
<br />
The most beautiful example of the last decade for this is, we call a web-service inside our trigger body. In case this is a web-service that actually 'does something' (changes/transacts information at/behind the endpoint of the service), then obviously when we rollback our triggering DML-statement, whatever was changed by the web-service, remains changed. Other examples of this use-case are: we send email from our trigger body, or perform file I/O. We can also perform an autonomous transaction (one that actually performs some DML which given it's executed inside the autonomous TX, cannot be rolled back by the parent transaction). This is actually a blend of this use-case with the previous one.<br />
<br />
So what's harmful about all these use-cases? Well, they make stuff happen <i>automagically</i> (copyright the other TK).<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjRCr86WWAl0utriRs25EXHoIwvxOF7bfgqubmxeFjcZ1bJFOLwL9PEufDPbQmS7-ndaeM8r7wte7H9_7VBCrViR3Knw8i2Pa73k2aMO9sDTOLfVknQ1iHBkA01T44Ffqa2K5FzaPUiRZH/s1600/30_cus5.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjRCr86WWAl0utriRs25EXHoIwvxOF7bfgqubmxeFjcZ1bJFOLwL9PEufDPbQmS7-ndaeM8r7wte7H9_7VBCrViR3Knw8i2Pa73k2aMO9sDTOLfVknQ1iHBkA01T44Ffqa2K5FzaPUiRZH/s400/30_cus5.jpg" width="400" /></a></div>
<br />
What we mean by this is, that instead of only getting the effect of the triggering DML-statement, you also get the effect of whatever is being performed inside the trigger body. And exactly that is the harmful bit.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhO8K6nRNg_tX5I2aKKLJA7Q_9K36YuKEw9vwSJr62Xo2v-hrGSHO3HJGBVAYpm5idVNYFKZx_ajcyO4J_o5zhXChQ4tdiU4v1x-H_tLRrtpoVvSbJg6aLXuBhrNG-7NFC-eTBwVB5Uy8eS/s1600/30_cus6.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhO8K6nRNg_tX5I2aKKLJA7Q_9K36YuKEw9vwSJr62Xo2v-hrGSHO3HJGBVAYpm5idVNYFKZx_ajcyO4J_o5zhXChQ4tdiU4v1x-H_tLRrtpoVvSbJg6aLXuBhrNG-7NFC-eTBwVB5Uy8eS/s400/30_cus6.jpg" width="400" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
In a SQL DBMS, the insert, update and delete 'operators', constitute three <i>primitive operators</i> that we can use to manipulate the data held inside the database (tables). Primitive here is used in the sense of <i>primary</i> or <i>basic</i>. Sort of like Inc(rement) and Dec(rement) are primitives that can be used in machine language to increase or decrease a register's value. These primitive operators have well understood and universally known <i>change-semantics</i>. Say you start with an empty EMP table, you then insert a employee row, you will end up with a change of the EMP table: it now has a single row in it. And all other bits and pieces of the database remain unchanged.<br />
<br />
Triggers executing automagic stuff change these universally well-understood change-semantics of our three primitive operators.<br />
<br />
And this is harmful: because nobody (after you've gone) expects insert, update or delete to quietly behave differently. For those of you who have done some machine programming in a distant past: it's like increasing one of your registers, and suddenly (as an automagic side-effect) the top of the execution stack is increased also and has some subroutine's address pushed on it. Not something you'd expect...<br />
<br />
The TCO (total cost of ownership) of an application sits not in initially building the thing. It sits in the years, sometimes decades, the thing lives. And you, but more probably others, maintain it. And more often than not these developers expect their available primitive operators to follow the primitive change-semantics.<br />
<br />
I'll finish this post with the following thought:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigVC-RonUZvevsz1KoGuc0rcsRa4tYH3K1aZsRxe7xflSjXEM_CxW-_wk-mtDpiiRDjrY46eX4ss_zAzHAQOTFblHvq_gAZ1VmDqW3k4SJl238qTNx_dnOZvZXDqWuH7Ib0msmCWxxTzNl/s1600/30_cus7.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="298" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigVC-RonUZvevsz1KoGuc0rcsRa4tYH3K1aZsRxe7xflSjXEM_CxW-_wk-mtDpiiRDjrY46eX4ss_zAzHAQOTFblHvq_gAZ1VmDqW3k4SJl238qTNx_dnOZvZXDqWuH7Ib0msmCWxxTzNl/s400/30_cus7.jpg" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
If you agree on the three use-cases discussed being harmful, do you then also agree with me that:</div>
<div class="separator" style="clear: both; text-align: left;">
</div>
<ul>
<li>The cascade delete option of a foreign key, and</li>
<li>The default clause of a table column, and</li>
<li>The char (fixed-length) datatype</li>
</ul>
<div>
are to be considered harmful too.</div>
<div>
<br /></div>
<div>
Aren't they conceptually about the exact same problem? Introducing different change-semantics to our three primitive operators?</div>
<div>
<br /></div>
<div>
I'd be interested to hear of any other 'features' that change the way insert, update or delete behave.</div>
<br />
<br />
<br />Toon Koppelaarshttp://www.blogger.com/profile/08153913435188725112noreply@blogger.com4tag:blogger.com,1999:blog-1339993448594009052.post-59463536381717677522012-01-29T08:13:00.000-08:002012-01-29T08:13:52.308-08:00Statement level constraint consistencyIn the past week I've been investigating how Oracle fires triggers with the Merge and Multi-Table-Insert statements. Also took a look at 'statement-level constraint consistency' with these two types of statements. My findings are here: <a href="http://rulegen.blogspot.com/2012/01/statement-level-constraint-validation.html">http://rulegen.blogspot.com/2012/01/statement-level-constraint-validation.html</a><br />
<br />
Normal transmission on harmful triggers should resume shortly.Toon Koppelaarshttp://www.blogger.com/profile/08153913435188725112noreply@blogger.com2tag:blogger.com,1999:blog-1339993448594009052.post-80152900854449126762012-01-04T06:12:00.000-08:002012-01-04T06:12:42.345-08:00Workaround 2: don't use the row-triggerSo where were we?<br />
<br />
We wanted to implement the following business rule: "Cannot have a manager without a clerk in the same department." At the end of <a href="http://harmfultriggers.blogspot.com/2011/11/some-preliminaries.html">this post</a>, we tried implementing that using a straight-forward row-trigger, which introduced us to the mutating table error. And we've explained thereafter (<a href="http://harmfultriggers.blogspot.com/2011/12/mutating-table-error-prevents-non.html">here</a> and <a href="http://harmfultriggers.blogspot.com/2011/12/look-mom-mutating-table-error-without.html">here</a>) that this error is there for a very good reason.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGREyExISq05Ihdfe9Zn7nbDTXzyVncV3kSfailorCtYALN_3OUGbsS8CPEdy2e79j8RJ8kBAU4KGcz0kKGz1h29w6OGwCbBgWHz5icVzDFt64v-PoBW1bNSnPOiYqGmbg0WoOefxW4lcf/s1600/12_mut1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGREyExISq05Ihdfe9Zn7nbDTXzyVncV3kSfailorCtYALN_3OUGbsS8CPEdy2e79j8RJ8kBAU4KGcz0kKGz1h29w6OGwCbBgWHz5icVzDFt64v-PoBW1bNSnPOiYqGmbg0WoOefxW4lcf/s400/12_mut1.jpg" width="400" /></a></div>
<br />
<br />
We've also clarified that the two common '<a href="http://harmfultriggers.blogspot.com/2011/11/workarounds-for-ora-04091.html">workarounds' 1a and 1b</a> really should not be used.<br />
<br />
So using a row-trigger to check for a clerk whenever a manager is inserted will not work. Remember the reason we wanted to use a row-trigger is because in it we have available <span style="font-family: 'Courier New', Courier, monospace;">:new.deptno</span> which represents the department into which a manager just got inserted. This enabled us to efficiently check only that department for the necessary clerk.<br />
<br />
So what about a statement trigger then? Obviously it forces us to validate all departments, since within the context of the after insert statement trigger, we do not know anything about the actual insert(s), other than that some insert took place.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjwToJCWls-LM29sQ9-tHaoJHhylYf9_ZcW0IrzNnRJMbVIl7FtHoKyrPGQwMa5kooJk4v9KtoVBbj4893gMoHA7EADfgYGUAjeH1_VPf5ho54GxIDL01m8bLQHE3Lwxk5EV2285_DAMl8J/s1600/24_workaround2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="298" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjwToJCWls-LM29sQ9-tHaoJHhylYf9_ZcW0IrzNnRJMbVIl7FtHoKyrPGQwMa5kooJk4v9KtoVBbj4893gMoHA7EADfgYGUAjeH1_VPf5ho54GxIDL01m8bLQHE3Lwxk5EV2285_DAMl8J/s400/24_workaround2.jpg" width="400" /></a></div>
<br />
Above is an attempt to implement our rule using the after insert statement trigger. As you can see we validate all departments. This is not a very efficient solution to implement our business rule. Not only do we validate all departments, we also validate them on all types of inserts. Obviously when we insert, for instance, a trainer, there is no need at all to validate this rule (assuming the rule was fine, before this insert), since inserting a trainer cannot violate this rule: such an insert doesn't 'touch' the rule.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5I6KHDaiz4wRj2rux9OCtjiquwL2VVnHPZ0Y6jy7uQgTZHWlldCZxjky6cP2u2mb8XCg67Mvua5vDK8qSAYQJovylthK-6TA0nWiauyuUoYHHTgQStb2lWrlcIgAU5liSTTeSFwIKAKDb/s1600/24_workaround2_1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="298" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5I6KHDaiz4wRj2rux9OCtjiquwL2VVnHPZ0Y6jy7uQgTZHWlldCZxjky6cP2u2mb8XCg67Mvua5vDK8qSAYQJovylthK-6TA0nWiauyuUoYHHTgQStb2lWrlcIgAU5liSTTeSFwIKAKDb/s400/24_workaround2_1.jpg" width="400" /></a></div>
<br />
We will explain in detail how to a) workaround the mutating table issue + b) get an efficient (and correct) implementation for our rule using a statement trigger in multiple future posts. But before we do that, we'd like to spend some time on common use-cases of triggers, and why they gave rise to the general consensus out there of triggers being harmful.<br />
<br />Toon Koppelaarshttp://www.blogger.com/profile/08153913435188725112noreply@blogger.com1tag:blogger.com,1999:blog-1339993448594009052.post-30259981776543371262011-12-30T01:10:00.000-08:002015-04-07T02:55:13.768-07:00Look mom: a mutating table error without a trigger!<div dir="ltr" style="text-align: left;" trbidi="on">
Did you ever wonder why it says 'trigger/function' in the error message of ORA-04091?<br />
<br />
<br />
<span style="font-family: 'Courier New', Courier, monospace;">ORA-04091: table ... is mutating, trigger/function may not see it</span><br />
<div>
<br /></div>
<br />
We know (and understand) by now that a row trigger cannot read a mutating table, but what's the /<i>function</i> all about in above message text? Well there is a completely different scenario that has nothing to do with triggers, where you can run into this error. I thought to spend a short post on that first, so that you really fully understand that ORA-04091 is your friend. And again in this scenario it prevents you from coding logic that might work differently tomorrow than from what it did today.<br />
<br />
Let's quickly show you this scenario. We create the EMP table again and insert some test data into it.<br />
<br />
<br />
<span style="font-family: 'Courier New', Courier, monospace;">drop table EMP;</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">create table EMP</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">(EMPNO number(3,0) not null primary key</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">,ENAME varchar2(20) not null</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">,SAL number(4,0) not null)</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">/</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">insert into emp(empno,ename,sal)</span><span style="font-family: 'Courier New', Courier, monospace;"> values(100,'Toon',4000);</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">insert into emp(empno,ename,sal)</span><span style="font-family: 'Courier New', Courier, monospace;"> values(101,'Izaak',5000);</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">insert into emp(empno,ename,sal)</span><span style="font-family: 'Courier New', Courier, monospace;"> values(102,'Marcel',7000);</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">insert into emp(empno,ename,sal)</span><span style="font-family: 'Courier New', Courier, monospace;"> values(103,'Rene',8000);</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">commit;</span><br />
<div>
<br /></div>
<br />
Our HR department decided that above salaries should be aligned a bit more in the year 2012. And in their wisdom they decided that the following update should be run at the end of the year:<br />
<br />
<br />
<span style="font-family: 'Courier New', Courier, monospace;">update EMP e1 set e1.SAL =</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> e1.SAL + ((select avg(e2.SAL) from EMP e2) - e1.SAL)/2</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">/</span><br />
<div>
<br /></div>
<div>
This update brings everybody's salary a bit more towards the current average salary of all employees. The dba does a testrun of the script in the acceptance instance:</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div>
<br /></div>
<div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhnzRUlD9xai4kLC4wINvDAoFlPDkUFkTPi-KEXI9B9MSDVeHFZNMjjqziusEG55ajMAuF_GOJC_dJqWrZRo-xuwYvkAGSUwJM8SRHxliW0rUhGHUql1A7H7lidzqKc6hZCIwFMZuLGq1_U/s1600/xx.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhnzRUlD9xai4kLC4wINvDAoFlPDkUFkTPi-KEXI9B9MSDVeHFZNMjjqziusEG55ajMAuF_GOJC_dJqWrZRo-xuwYvkAGSUwJM8SRHxliW0rUhGHUql1A7H7lidzqKc6hZCIwFMZuLGq1_U/s400/xx.jpg" height="331" width="400" /></a></div>
<br /></div>
<div>
It runs well, HR confirms that it does what it was intended to do, and dba is fine with this in production.<br />
<br />
Here come the QA department though: they need to 'ok' the running of this script in production too. So they inspect the script with all QC guidelines in mind. Result of their review is that they won't allow this. There is distinct 'business logic' in there that needs to be removed from the statement and implemented in a stored function, so that it 'can be reused sometime in the future' (I've seen this happen in real companies...). They mandate that a function be implemented such that the update statement will be of the following form:<br />
<br />
<br />
<span style="font-family: 'Courier New', Courier, monospace;">update EMP e set e.SAL = f_new_sal(e.SAL)</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">/</span><br />
<br />
So the developers pump out the following function for this:<br />
<br /></div>
<br />
<span style="font-family: 'Courier New', Courier, monospace;">create or replace function f_new_sal</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">(p_current_sal in number) </span><span style="font-family: 'Courier New', Courier, monospace;">return number as</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">--</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">pl_avg_sal number;</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">--</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">begin</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> --</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> select avg(SAL) </span><span style="font-family: 'Courier New', Courier, monospace;">into pl_avg_sal</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> from EMP;</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> --</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> return p_current_sal + (pl_avg_sal - p_current_sal)/2;</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> --</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">end;</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">/</span><br />
<br />
<br />
Everybody is happy. The dba executes the testrun again:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjX1_cS8mHGBTgusnCmzLOio_lmo5uLl6pLIR-_6A9_upTJzcgYvdbRDTQIsvAADvwG49TLku4whdKd3zot1MIVqn8Mlc9A8AUiU6lIHLQCgxqz-DqiuTTPbx-MxMvUkVhyphenhyphen118tmtxiKOeN/s1600/23_testrun2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjX1_cS8mHGBTgusnCmzLOio_lmo5uLl6pLIR-_6A9_upTJzcgYvdbRDTQIsvAADvwG49TLku4whdKd3zot1MIVqn8Mlc9A8AUiU6lIHLQCgxqz-DqiuTTPbx-MxMvUkVhyphenhyphen118tmtxiKOeN/s400/23_testrun2.jpg" height="245" width="400" /></a></div>
<br />
<br />
WTF? Oracle throws a mutating table error? But there's no trigger involved here...<br />
<br />
Why is this happening?<br />
<br />
If you understood the explanation of this error in my previous posts sofar, you should be able to figure this one out. Oracle again detects that we seem to be selecting from a table that's currently mutating. Think about what is happening here:<br />
<br />
<ul>
<li>The update starts identifying the rows that need their SAL column value changed;</li>
<li>It does this in some undetermined order (!);</li>
<li>When the first rows is found, it invokes our function to determine the new SAL column value for it;</li>
<li>Our function queries the EMP table, which is currently mutating: bingo we hit the error.</li>
</ul>
<div>
<br />
When Oracle steps out of the SQL-engine that's currently executing the update statement, and invokes the function, then this function -- just like an after row update trigger would -- sees the intermediate states of EMP as they exist during execution of the update statement. This implies that the return value of our function invocations heavily depend on the order in which the rows happen to be updated.</div>
<div>
<br /></div>
<div>
Wanna see this happening? Let's use our database link "workaround" to prevent the mutating error. We create a loopback database link and change our function accordingly:</div>
<div>
<br /></div>
<div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">create or replace function f_new_sal</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">(p_current_sal in number) return number as</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">--</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">pl_avg_sal number;</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">--</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">begin</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> --</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> select avg(SAL) into pl_avg_sal</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> from EMP@loopback; -- Here: added db-link.</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> --</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> return p_current_sal + (pl_avg_sal - p_current_sal)/2;</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> --</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">end;</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">/</span></div>
</div>
<div>
<br /></div>
<div>
Dba can now run the update without hitting the error:</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgb9unoa7ahUjFXDCLCGr6B0QMNwpYc3LnNvBIpjx90eQt9pxlL0FXIHXoilaFtafLvRB_tOA2HJG436oJYLU4IlpFvcX1vJQtjpI7frfu-9nDin551KJ4_L74C24vC1ekJaxX48kaubqVy/s1600/23_testrun3.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgb9unoa7ahUjFXDCLCGr6B0QMNwpYc3LnNvBIpjx90eQt9pxlL0FXIHXoilaFtafLvRB_tOA2HJG436oJYLU4IlpFvcX1vJQtjpI7frfu-9nDin551KJ4_L74C24vC1ekJaxX48kaubqVy/s400/23_testrun3.jpg" height="372" width="400" /></a></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
But look at the SAL values, only Toon's salary is updated correctly. These are not accepted by HR...</div>
<div>
<br /></div>
<div>
And these values will be different if we force a different order of updating the rows, for instance like this:</div>
<div>
<br /></div>
<div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">update (select * from EMP order by EMPNO desc) e </span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"> set e.SAL = f_new_sal(e.SAL)</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;">/</span></div>
</div>
<div>
<br /></div>
<div>
The proof is in eating the pudding:</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiS6NShccH4QVBXQP0HGUG28UWdkNbqPMKW4Njsn3HoCKuCRxalIwp7UAS25Vdliz9NDWi6l0LoOupRCcFMLvwAfH1DumHxPkjOXHlGoKyJrtz9wpUUUQelFZ6ejnv5SAKS4VTXnCIwjv2u/s1600/23_testrun4.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiS6NShccH4QVBXQP0HGUG28UWdkNbqPMKW4Njsn3HoCKuCRxalIwp7UAS25Vdliz9NDWi6l0LoOupRCcFMLvwAfH1DumHxPkjOXHlGoKyJrtz9wpUUUQelFZ6ejnv5SAKS4VTXnCIwjv2u/s400/23_testrun4.jpg" height="400" width="373" /></a></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
Now Rene's salary is the only one being updated correctly.</div>
<div>
<br /></div>
<div>
So again, don't forget: ORA-04091 is your friend.</div>
<div>
<br /></div>
<div>
Note that in the original version of the update statement (without the function), Oracle ensures that the repeated 'invocations' of the subquery (select AVG(e2.SAL) etc.) all see the same snapshot of the database: the one that existed at the start of the update statement. So there is no indeterministic behavior in that case.</div>
<div>
<br /></div>
<div>
A fix for the issue demonstrated could be to just add the autonomous transaction pragma to the function. We would then get the same (deterministic) behavior as the original version of the update statement.</div>
<div>
A better fix in this case though would be to not have the function at all, and just go with the original update statement.</div>
<div>
<br /></div>
</div>
Toon Koppelaarshttp://www.blogger.com/profile/08153913435188725112noreply@blogger.com4tag:blogger.com,1999:blog-1339993448594009052.post-16861681324735948882011-12-23T01:13:00.000-08:002011-12-23T01:13:41.012-08:00The mutating table error prevents non-deterministic behavior of your codeA short recap of workarounds 1a and 1b from our <a href="http://harmfultriggers.blogspot.com/2011/11/workarounds-for-ora-04091.html">previous post</a>.<br />
<ul>
<li>In workaround 1a we ensured that the query that was selecting from the mutating table, was being executed from within an autonomous transaction.</li>
<li>In workaround 1b we ensured that this query was being executed via a loopback database link.</li>
</ul>
<div>
We also discussed the major difference between the two workarounds:</div>
<div>
<ul>
<li>1a: the query sees the mutating table <i>as it existed prior to the start of the main transaction</i>.</li>
<li>1b: the query sees the mutating table <i>as it is being mutated by the triggering DML statement</i>. Essentially this workaround behaves as if Oracle did not have a mutating table error at all.</li>
</ul>
</div>
Please never implement these workarounds. Especially 1b for the reason I will demonstrate in this post. And do you really want the overhead of going through a database link every time the row-trigger fires? And potentially have twice the number of database sessions in your database instance? I hope not.<br />
<br />
Apart from this, you will have planted a booby trap in your application code: your code can now behave non-deterministically.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilLWLtgGRyxhBBzb68r6OH3aF1sNCZQLeBh4SanrJKyH_v-2lbkjXl6fRTvk1g76YUQVrQvDic0qkIyMTmM0Pry8wW4ZK3e4uj06AccHrs4BvodNf2ZXYkUa9Leru5S1na-xGgdkWSvmK0/s1600/19_why_friend.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="298" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilLWLtgGRyxhBBzb68r6OH3aF1sNCZQLeBh4SanrJKyH_v-2lbkjXl6fRTvk1g76YUQVrQvDic0qkIyMTmM0Pry8wW4ZK3e4uj06AccHrs4BvodNf2ZXYkUa9Leru5S1na-xGgdkWSvmK0/s400/19_why_friend.jpg" width="400" /></a></div>
<br />
To discuss this we will investigate how our row-trigger behaves not in the case of a <i>single row</i> insert, but a <i>multi-row</i> insert. Given our business rule, no manager can be without a clerk in the same department, a multi-row insert that inserts both the manager as well as the necessary clerk as the first two employees for a new department in one go, is a valid insert statement that our trigger-based rule-enforcement code should allow.<br />
<br />
Let's look at the behavior of our row-trigger using workaround 1b (loopback db-link) first. Suppose we have a table called EMP_STAGE. This (staging) table is holding employee-rows that are to be inserted into the real EMP table. Table EMP_STAGE currently holds two employees: a manager and a clerk both working in department 42.<br />
<br />
Now see what our row-trigger does.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhklMUcU9NaM-972OkjlVQ9raRsdw4Ki_kls1gitKRe660BQ_kChO-qwNUdB14IRDrP5QmkQ-ipMZrkwIr157hJjgJnIyZck_aqCuI_Hofo2eHkWm7RuM6c_6wjSF4bH5NWMlav8GPj3I-o/s1600/20_flaw1b.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhklMUcU9NaM-972OkjlVQ9raRsdw4Ki_kls1gitKRe660BQ_kChO-qwNUdB14IRDrP5QmkQ-ipMZrkwIr157hJjgJnIyZck_aqCuI_Hofo2eHkWm7RuM6c_6wjSF4bH5NWMlav8GPj3I-o/s400/20_flaw1b.jpg" width="400" /></a></div>
<br />
Our row-trigger does not allow this insert. Why? Well think about it. How is the statement 'insert into EMP select * from EMP_STAGE' executed? Particularly in what order are the selected from EMP_STAGE and then inserted into EMP? Apparently in the case above the manager was the first one that was selected and inserted into EMP. Which would then fire our row-trigger, which would then see the mutating table (via the loopback db-link) and not find the required clerk in department 24. Hence our row-trigger raised the business rule error. And the multi-row insert statement never reaches inserting the second row (the clerk), it is cancelled by the raise_application_error and rolled back.<br />
<br />
But what if the rows happened to be processed in a different order? We can force this by simply adding an order-by clause to the subquery in the insert statement (we re-execute the insert and have added 'order by empno desc'):<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjaljhR-Zsk0aws0M0No_9R3hQygidoIuW7QDoJ6ok2MmHCXYNXnNqXcLnjfcSRBpaI6nZHD3DqshYP0RN2n6cSU5rGT2tISj598QzofI2xMfhCqCSGXfj6sKIhdQu3c4zpq4lIvYlEpr6q/s1600/21_reason1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjaljhR-Zsk0aws0M0No_9R3hQygidoIuW7QDoJ6ok2MmHCXYNXnNqXcLnjfcSRBpaI6nZHD3DqshYP0RN2n6cSU5rGT2tISj598QzofI2xMfhCqCSGXfj6sKIhdQu3c4zpq4lIvYlEpr6q/s400/21_reason1.jpg" width="400" /></a></div>
<br />
Our two-row insert statement now executes succesfully!<br />
<br />
So what happened in this case? Due to the order by clause we've ensured that employee 111 (the clerk) got inserted first. The row-trigger fires, sees it's not a manager and therefore nothing needs to be done. Next employee 110 got inserted. The row-trigger fires again, sees it's a manager, and executes the business rule check by calling our procedure. This procedure now queries the EMP table (via the loopback db-link) and it actually sees (!) the clerk that just got inserted by the same statement, and therefore executes successfully.<br />
<br />
Now the order in which rows get inserted by a multi-row insert statement is (and rightfully should be) of no concern to you. Think of the subquery instead of being a simple 'select * from emp_stage', being a complex multi-table join: depending on current contents/sizes of the tables involved, statistics might differ which in turn might cause different execution plans for the insert statement, which in turn might change the order in which rows get inserted by a multi-row insert statement. <i>If your code is not immune to this order then in effect your code executes in a non-deterministic fashion.</i> And this is the essence of why ORA-04091 is your friend. It protects you against developing software that might work today, and not work tomorrow.<br />
<br />
So how does this all work with the autonomous transaction workaround? Here's the same two-row insert for that scenario, executed without and with the forced order (EMP again has no employees in department 42).<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgeWfogykcSxt_xfPp5gDauBH-aD8Sx2-OuTGobdAQlyHSHDr5-KLxcDgOtTW2txvSydaVAICutHNuqnSwzW1ePrEaynQULOeWr8zR5IKtokeRTZfcwweaihNpbxcTLQLh5hbA_Iwggumgf/s1600/21_reason2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgeWfogykcSxt_xfPp5gDauBH-aD8Sx2-OuTGobdAQlyHSHDr5-KLxcDgOtTW2txvSydaVAICutHNuqnSwzW1ePrEaynQULOeWr8zR5IKtokeRTZfcwweaihNpbxcTLQLh5hbA_Iwggumgf/s400/21_reason2.jpg" width="400" /></a></div>
<br />
As you can see both versions of the insert statements fail with this workaround. The explanation for this is simple. Since the query that checks for the necessary clerk is run from inside an autonomous transaction, all it sees is the EMP table as it existed prior to start of the insert statement: in which there were no employees for department 42. So in this case the 'workaround' is not a workaround at all: yes it prevents the mutating table error, but no it does not allow a valid insert-statement to execute successfully. Note that it even wouldn't allow two subsequent single row-inserts: first insert a clerk, then insert a manager. With this workaround you are forced to commit the clerk first, and only then insert the manager.<br />
<br />
So, in summary:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyDT9GItF71PskuP-OFeW8cLx_RoGGxpdayoHH3O24TjZlz_dqcSRe-ur1G08VcpisHhoNLGEOSqLPgSGaImj6IxzOtmHvNEMjNL8nIncAkCGvi4L0JFJ5tGxR-Nzxl2ut4XpBnKX9I2g7/s1600/22_summary.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyDT9GItF71PskuP-OFeW8cLx_RoGGxpdayoHH3O24TjZlz_dqcSRe-ur1G08VcpisHhoNLGEOSqLPgSGaImj6IxzOtmHvNEMjNL8nIncAkCGvi4L0JFJ5tGxR-Nzxl2ut4XpBnKX9I2g7/s400/22_summary.jpg" width="400" /></a></div>
<br />
Again, forget about these two red herrings: don't use them.<br />
<br />
In the following posts we will start developing how to correctly work around the mutating table error. And as many of you probably know, it involves a combination of row and statement triggers.<br />
<br />
In the meantime happy holidays!<br />
<br />Toon Koppelaarshttp://www.blogger.com/profile/08153913435188725112noreply@blogger.com4tag:blogger.com,1999:blog-1339993448594009052.post-72274474563306350072011-12-13T22:35:00.000-08:002011-12-13T12:41:28.722-08:00"Workarounds" for ORA-04091In the <a href="http://harmfultriggers.blogspot.com/2011/11/some-preliminaries.html">previous post</a>, we demonstrated running into the mutating table error. The example involved the well-known EMP table and a business rule that we are trying to implement: all departments with a Manager should also employ a Clerk. We started our implementation for that rule by writing a straight-forward 'after-row-insert' trigger on the EMP table.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhB3_SLX2J0i_OI72c5M3YMLOYWLczNhfLLycMruRNgboCtDjmjpNvimRjGS21oSZDRhxy4ZUd9gShZBbx2vEq_i8dTegaEMItbzgW6yaAvufXET-FaSNvLsN7GGhsRJFgRAmfPo4T78FPV/s1600/13_mut2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="301" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhB3_SLX2J0i_OI72c5M3YMLOYWLczNhfLLycMruRNgboCtDjmjpNvimRjGS21oSZDRhxy4ZUd9gShZBbx2vEq_i8dTegaEMItbzgW6yaAvufXET-FaSNvLsN7GGhsRJFgRAmfPo4T78FPV/s400/13_mut2.jpg" width="400" /></a></div>
<br />
<ul>
<li>The row trigger detects whenever a Manager is inserted (remember, we can only detect this with a row trigger, not with a statement trigger);</li>
<li>It then calls a stored procedure to which it supplies the department number (deptno-column value) of the inserted Manager;</li>
<li>The procedure then queries the EMP table to see if a Clerk exists in this department;</li>
<ul>
<li>If it finds no such Clerk, the procedure raises an application error, which causes the after-insert-row trigger to fail. This in turn prevents the insert of the Manager, since it would violate our business rule (the Manager insert will undergo a statement-level rollback due to the row-trigger failing);</li>
<li>If it finds such a Clerk, then the procedure finishes successfully, which causes the after-insert-row trigger to finish successfully, which causes the Manager insert to execute successfully.</li>
</ul>
</ul>
<div>
A straight-forward 'use-case' of using a (row) trigger, right? Of course this setup didn't work, since the cursor in the stored procedure will always hit the mutating table error, thus preventing a Manager insert even if the necessary Clerk would indeed be present.</div>
<div>
<br /></div>
<div>
So, what's the workaround for this? Well there are a two I would like to discuss in this post: one of them is very 'popular', the other one is -- I guess -- not so well-known.</div>
<div>
<br /></div>
<div>
I cannot stress enough upfront right now, that both 'workarounds' are red herrings.</div>
<div>
<br /></div>
<div>
<b>Workaround 1a: use the autonomous_transaction pragma inside the stored procedure.</b></div>
<div>
<br /></div>
<div>
If we add just one line at the top of the stored procedure, we can fix this baby (see red-arrow line below).</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjoiSN7LO1Z_60n2nZEOtjgOCBAI6HXiCMLbiCxtDyljieVMPQpU9ae0xn1YuKgSmL3BmkikdWHWSUS_pzr9-AbY6I4UThP68Ce9NG3dGsxBcpA7K1AS5H2EKRGBcXrLw3sMKRtji6YZBUu/s1600/16_workaround1a.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="296" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjoiSN7LO1Z_60n2nZEOtjgOCBAI6HXiCMLbiCxtDyljieVMPQpU9ae0xn1YuKgSmL3BmkikdWHWSUS_pzr9-AbY6I4UThP68Ce9NG3dGsxBcpA7K1AS5H2EKRGBcXrLw3sMKRtji6YZBUu/s400/16_workaround1a.jpg" width="400" /></a></div>
<div>
<br /></div>
<div>
By ensuring that all code inside the P_CHECK_FOR_CLERK procedure will run as an autonomous transaction, we can prevent the mutating table error. Why? Because it causes Oracle to 'step out' of the current transaction context, in which EMP is currently 'mutating', and open a new transaction in which EMP is not mutating, and therefor can be queried without hitting the mutating table error. So all we need to do is add the PRAGMA line highlighted above. We do not need to add an explicit COMMIT (or ROLLBACK) at the end of the procedure, which is usually required to end an autonomous transaction, since the procedure doesn't really start a transaction at all: is just issues a query.</div>
<div>
<br /></div>
<div>
This is the popular workaround. Now for the lesser known workaround.</div>
<div>
<br /></div>
<div>
<b>Workaround 1b: use a loopback db-link to query the mutating table.</b></div>
<div>
<br /></div>
<div>
By querying the EMP table to look for a Clerk, via a loopback database link, we can fix this baby too.</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdE_pSs7rN_gU2h1nYkwY6iDjdqyBcYO8ytutAAplKqqHsThyonE2ya_ApYXpgcAS2EwSZvT1QhIOcXQ1HRYmZ7hEG3lHg7x-i0k9YzW7mGZ_Q9n1zUezZl8iY1xr5ePUyqsh7G5DebjYV/s1600/16_workaround1b.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="298" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdE_pSs7rN_gU2h1nYkwY6iDjdqyBcYO8ytutAAplKqqHsThyonE2ya_ApYXpgcAS2EwSZvT1QhIOcXQ1HRYmZ7hEG3lHg7x-i0k9YzW7mGZ_Q9n1zUezZl8iY1xr5ePUyqsh7G5DebjYV/s400/16_workaround1b.jpg" width="400" /></a></div>
<div>
<br /></div>
<div>
So we create a loopback database link (which we call loopback above) which connects back to our application owner schema. We then add the text '@loopback' to the table name (EMP) inside the FROM-clause of our query that was hitting the mutating table (second red arrow above). Done. We'll demonstrate in a minute that this 'fixes' our issue as well.</div>
<div>
<br /></div>
<div>
But before we do that you really need to understand a fundamental difference between these two workarounds.</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfM92TnQJ4hghemsJWyFfw2JDcEweu4V9_o5cYBdp3Cn2oGWeQn-Kw39OnclqCR5sLHZqxWWKSckSFDruvCNql9b3JCC10NuWbR62giUzv3AnQ85FbNduFk0rriON-3cQ1F7wmCVFlYLIz/s1600/17_beware.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfM92TnQJ4hghemsJWyFfw2JDcEweu4V9_o5cYBdp3Cn2oGWeQn-Kw39OnclqCR5sLHZqxWWKSckSFDruvCNql9b3JCC10NuWbR62giUzv3AnQ85FbNduFk0rriON-3cQ1F7wmCVFlYLIz/s400/17_beware.jpg" width="400" /></a></div>
<div>
<br /></div>
<div>
In the preliminaries that we discussed in the previous post, one of the observations that was made about an autonomous transaction was that all code in such transaction cannot see any (uncommitted) changes made by the other (parent) transaction. So what this means for our example is that the cursor in the stored procedure (the one that tries to find a Clerk) which is executed by an 'after-row' trigger, will see the EMP table without the Manager that was inserted by our triggering insert statement.</div>
<div>
<br /></div>
<div>
Yet.</div>
<div>
<br /></div>
<div>
The cursor that uses the loopback database link, which is executed from within a new session created by the database link, *will* see the mutating table as it exists at that point-in-time in the transaction of the 'original' session (the one that issued the Manager insert). Why? Because Oracle detects that both sessions are now part of a distributed transaction, and the database link session will see the same database snapshot as the other session. Now this is pretty radical, I think. When I discovered this behavior I was stunned for a few moments: here we have a database session (OK, it's one that was created by a database link, but still) a session that is able to see uncommitted changes of a different session! I will show you a demo that proves this behavior in a minute.</div>
<div>
<br /></div>
<div>
Now wouldn't that be an in-depth (trick) question for some fancy certification exam:</div>
<div>
<br /></div>
<div>
"When can a database session see uncommitted changes of another session?"</div>
<div>
a) Never.</div>
<div>
b) Always, this is default behavior.</div>
<div>
c) If it's using an autonomous transaction.</div>
<div>
d) If the session was opened by a database link.</div>
<div>
<br /></div>
<div>
;-)</div>
<div>
<br /></div>
<div>
Now let's test both workarounds. Remember we are trying to insert a manager into department 42, in which no other employee works yet.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiJfqNg1UruYsoFZoaWQNojbzDprwIRmIt0EyBHdY1LN_qrAWwcploMd5aIO6lXafsPJb2SKEzBYoOXc3lAJZ4mLG-V6VtGathqGCdcDSQpo_bGKsahAQyAJeqLqeqUwIMERfH6WnPh3OQg/s1600/18_itworks.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="302" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiJfqNg1UruYsoFZoaWQNojbzDprwIRmIt0EyBHdY1LN_qrAWwcploMd5aIO6lXafsPJb2SKEzBYoOXc3lAJZ4mLG-V6VtGathqGCdcDSQpo_bGKsahAQyAJeqLqeqUwIMERfH6WnPh3OQg/s400/18_itworks.jpg" width="400" /></a></div>
<br /></div>
<div>
<br />
Et voilá: no more mutating table error. Our row-trigger + procedure work, they give us the business rule error.<br />
<br />
So what's wrong with both approaches? Can you guess it? I'll give you a hint: think about multi-row inserts. Will they work given workaround 1a (the autonomous transaction) or workaround 1b (the db-link trick)?<br />
<br />
I'll discuss this in the sequel, which should follow promptly.<br />
<br /></div>Toon Koppelaarshttp://www.blogger.com/profile/08153913435188725112noreply@blogger.com6tag:blogger.com,1999:blog-1339993448594009052.post-48322264210150217972011-11-25T13:13:00.001-08:002011-11-25T14:16:31.847-08:00Some preliminariesThere are a few remarks to be made on DML event triggers. Just to be sure we are all at the same level on the playing field. The first one is that since these triggers fire as part of the execution of a DML statement, the trigger code is not allowed to execute any DDL (which causes an implicit commit) or (explicit) transactional control statements, such as commit, rollback, savepoint or rollback to savepoint. This restriction is true for both statement level as well as row level DML event triggers.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpCpW_OJMa23Xrm9F4hrCBK2Torl3wI7mPKlcOjQKF2x2CWQuf6suJJiSjErMq3kW4SgD3dAV7CUf2HXVY7SX2Ql3eg-Z9bzlvPvt4DKBI22t4KOkkw5iNmFADWmqU5MXF25nKXh9qS27J/s1600/09_prop1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="297" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpCpW_OJMa23Xrm9F4hrCBK2Torl3wI7mPKlcOjQKF2x2CWQuf6suJJiSjErMq3kW4SgD3dAV7CUf2HXVY7SX2Ql3eg-Z9bzlvPvt4DKBI22t4KOkkw5iNmFADWmqU5MXF25nKXh9qS27J/s400/09_prop1.jpg" width="400" /></a></div>
<br />
If you try to do so, Oracle will throw an <a href="http://ora-04092.ora-code.com/">ORA-04092</a> at you. A common "workaround" of developers who really think that they need to do this kind of stuff from within trigger code, is to wrap the offending code inside an autonomous transaction, thereby not affecting, transactionally, the main transaction which is currently executing the DML statement (that caused the trigger to fire). There is an important observation to be made at this time: the SQL-code inside such autonomous code block, cannot see the changes that are being made by the DML statement that is executing. Nor can this SQL-code see the effect of any other DML statements that might have been executed earlier inside the main transaction. Oracle treats the autonomous code block as if it were executed inside a different <i>session</i>. And sessions never see changes made by other sessions that have not yet committed these changes.<br />
<br />
Remember this one. I'll refer back to this observation in the next post when I discuss common "workarounds" for the infamous mutating table error.<br />
<br />
The second remark:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgriKNty0lGsXSEg1_6wQd3QwFlXAeAhxtmGy6Ql_XLKgQlJY3CMZ07ay-nM4nXDPRdC3H7bTaBpADg20WikwL8J_WDeT0K92jmXTxP-7QyyR558lKCOgvvS7SnK4eLZ3sxhC-qUsGmAZzX/s1600/10_prop2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgriKNty0lGsXSEg1_6wQd3QwFlXAeAhxtmGy6Ql_XLKgQlJY3CMZ07ay-nM4nXDPRdC3H7bTaBpADg20WikwL8J_WDeT0K92jmXTxP-7QyyR558lKCOgvvS7SnK4eLZ3sxhC-qUsGmAZzX/s400/10_prop2.jpg" width="400" /></a></div>
<br />
If a trigger fails, due to an exception being raised (and left unhandled), then the triggering DML statement (and all that might have been done by the trigger code self), will be rolled back. Note that this is a statement level rollback: so any prior changes executed in the transaction, are still left "posted" (not rolled back). You can view this statement level rollback as a 'rollback to savepoint', where the savepoint was set (by Oracle) just prior to starting the DML statement execution.<br />
<br />
Third (an final) remark in this post: row level triggers are not allowed to read what's called the mutating table.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsx4ouPF1H2abivvwRTRnA-0LdSJwzmfo8ILr2Gts8-Rea_qA4Ugwr-puqncWP57t1dhAuHSq10g0ux5uYWw-8IhJfzL8MyRWXeFScXhUhjkZkcCHR3WRX25GwfB3QhjL6kM5HGwjJrlEI/s1600/11_prop3.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="298" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsx4ouPF1H2abivvwRTRnA-0LdSJwzmfo8ILr2Gts8-Rea_qA4Ugwr-puqncWP57t1dhAuHSq10g0ux5uYWw-8IhJfzL8MyRWXeFScXhUhjkZkcCHR3WRX25GwfB3QhjL6kM5HGwjJrlEI/s400/11_prop3.jpg" width="400" /></a></div>
<br />
If your row level trigger code queries the table that's being affected by the triggering DML statement, then Oracle will throw an <a href="http://ora-04091.ora-code.com/">ORA-04091</a> at you and cause the above mentioned statement level rollback. If you've ever played around with (row) triggers, you must have encountered this issue. I think my first encounter was in 1993. And there is a very clear cause why you will encounter this issue: the kind of stuff you would like to do in a row-trigger, often, no very often, requires you to query the mutating table. I'll come back to this later.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZKpjclV_dPw_k-ApUOrMy5bse5GBnHbBBHhWR5-AbdTIGruk0BRlfa1CadfPUBq_QpyBHacIA5UnlYKGhBwxTEuRI2b9kj0IaRm95ctaRN5AvfZO1aQpGepNd2FsubefKyczAiDyoSmk2/s1600/12_mut1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZKpjclV_dPw_k-ApUOrMy5bse5GBnHbBBHhWR5-AbdTIGruk0BRlfa1CadfPUBq_QpyBHacIA5UnlYKGhBwxTEuRI2b9kj0IaRm95ctaRN5AvfZO1aQpGepNd2FsubefKyczAiDyoSmk2/s400/12_mut1.jpg" width="400" /></a></div>
<br />
One of my goals is to explain to you that this error is not your enemy. No, it's your friend. It prevents you to develop software, that might work today, but no longer tomorrow. I'll demonstrate that in the next post. For now we'll close this post by quickly building an example that gives rise to the mutating table.<br />
<br />
Suppose we need to implement the requirement that every employee whose job is Manager, must be accompanied by another employee whose job is Clerk and works in the same department (as the manager does). Well, we can implement that easily, all it requires is a row trigger, right?<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhB3_SLX2J0i_OI72c5M3YMLOYWLczNhfLLycMruRNgboCtDjmjpNvimRjGS21oSZDRhxy4ZUd9gShZBbx2vEq_i8dTegaEMItbzgW6yaAvufXET-FaSNvLsN7GGhsRJFgRAmfPo4T78FPV/s1600/13_mut2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="301" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhB3_SLX2J0i_OI72c5M3YMLOYWLczNhfLLycMruRNgboCtDjmjpNvimRjGS21oSZDRhxy4ZUd9gShZBbx2vEq_i8dTegaEMItbzgW6yaAvufXET-FaSNvLsN7GGhsRJFgRAmfPo4T78FPV/s400/13_mut2.jpg" width="400" /></a></div>
<br />
We build an after insert row trigger on the EMP table, which will verify, whenever a Manager is inserted, that a Clerk exists in the same department. Here's the code for that procedure you see called in above trigger body:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjfZcV90fkBEvS5lxH7E_B-8gcv2376aiKu4kQeCN_DnqCA04YvebwQImyVqlx6Sb58F927q_vgeOBcRLWnvdtslFJJGndkrsJ56EjiJYTJf0aykn_Xt5oEqGLodr16SUXAth3OGvCKWEx8/s1600/14_mut3.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjfZcV90fkBEvS5lxH7E_B-8gcv2376aiKu4kQeCN_DnqCA04YvebwQImyVqlx6Sb58F927q_vgeOBcRLWnvdtslFJJGndkrsJ56EjiJYTJf0aykn_Xt5oEqGLodr16SUXAth3OGvCKWEx8/s400/14_mut3.jpg" width="400" /></a></div>
<br />
And as you can see, this code queries the mutating table. So let's test this trigger. Here's the contents of our EMP table, and an insert statement that attempts to insert an Manager into department 42 (which has no Clerk).<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZZa_7GdE40rFGaXtkH3mLjwfy5wQazJcJlnUscIUwF-nMWkBJnGLtIoJpDUU93asYpCz1OGWUktzKONzfv24goPhTjBXd0QeZs918HMHymIVk-ypBDs7s4bY94ppM3fPfJg1tjlDBI3gK/s1600/15_mut4.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZZa_7GdE40rFGaXtkH3mLjwfy5wQazJcJlnUscIUwF-nMWkBJnGLtIoJpDUU93asYpCz1OGWUktzKONzfv24goPhTjBXd0QeZs918HMHymIVk-ypBDs7s4bY94ppM3fPfJg1tjlDBI3gK/s400/15_mut4.jpg" width="400" /></a></div>
<br />
And as expected, Oracle throws the mutating table at us.<br />
<br />
In the next installment I'll discuss common "workarounds" for this issue. They really aren't workarounds, but everyone calls them that...<br />
<br />Toon Koppelaarshttp://www.blogger.com/profile/08153913435188725112noreply@blogger.com9tag:blogger.com,1999:blog-1339993448594009052.post-25332646283083577512011-11-22T07:26:00.001-08:002011-11-22T09:53:53.929-08:00So what triggers are we talking about?Here's an overview of the talk.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDzudPYhAjcM3lMQqqyYNu8xmBsctHtx71guYpNkujhBAgkT6YOe5Z0Pt4Z7ahFu2pD_Fv9AcddeuKdKa_u2XN1BPSaqbhM5vk0y_0cCKjBz5h4Bxl8z6E_7s6mVX6YMFXGdRS2XgPQEUV/s1600/01_contents.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="298" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDzudPYhAjcM3lMQqqyYNu8xmBsctHtx71guYpNkujhBAgkT6YOe5Z0Pt4Z7ahFu2pD_Fv9AcddeuKdKa_u2XN1BPSaqbhM5vk0y_0cCKjBz5h4Bxl8z6E_7s6mVX6YMFXGdRS2XgPQEUV/s400/01_contents.jpg" width="400" /></a></div>
<br />
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.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEguSY-EaR8nvQptnc_XAm8LtWdbeIDXcOwNup1wHt1hN-w_IzzHUv8kOgputaMVtOwaXBhQsxcd-lgmPz8t33NyiZA5q3RtZ6jU2-jLfNzYRW2VhKEcCS8yr3tnAnzzs2o-_8jm8qdx6OS4/s1600/01_whattriggers.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEguSY-EaR8nvQptnc_XAm8LtWdbeIDXcOwNup1wHt1hN-w_IzzHUv8kOgputaMVtOwaXBhQsxcd-lgmPz8t33NyiZA5q3RtZ6jU2-jLfNzYRW2VhKEcCS8yr3tnAnzzs2o-_8jm8qdx6OS4/s400/01_whattriggers.jpg" width="400" /></a></div>
<br />
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.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiaN0u9hejahgyNWn1Kwm9BIHvIQZT3G-_SJJjLDFNJsbDnA0T1yJyYFZ4nPNXPtSl9F8C10a0-NKawWhynvDMOGGsNH1KckOxg_1282L9VGWBhHHxs4utWF6KogD1So0-S3ihbSsiJo3O2/s1600/03_example.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="298" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiaN0u9hejahgyNWn1Kwm9BIHvIQZT3G-_SJJjLDFNJsbDnA0T1yJyYFZ4nPNXPtSl9F8C10a0-NKawWhynvDMOGGsNH1KckOxg_1282L9VGWBhHHxs4utWF6KogD1So0-S3ihbSsiJo3O2/s400/03_example.jpg" width="400" /></a></div>
<br />
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.<br />
<br />
So if we create the four update triggers on the EMP table as follows:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjj-PIc60QwZ8ZVfXlpDSw-cIlj9Pa4KiYjkV8wFr49xilDYJRtzxORqbpxhkbqOCiB5x_AxzIxn8UWQFoIB13sVByvv1dOqI4EX1bKidTWHjKWdPQDZoClmdZfyuXu3QVgYcaDJd5xPDzI/s1600/04_triggs.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="297" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjj-PIc60QwZ8ZVfXlpDSw-cIlj9Pa4KiYjkV8wFr49xilDYJRtzxORqbpxhkbqOCiB5x_AxzIxn8UWQFoIB13sVByvv1dOqI4EX1bKidTWHjKWdPQDZoClmdZfyuXu3QVgYcaDJd5xPDzI/s400/04_triggs.jpg" width="400" /></a></div>
<br />
We will get the following output (given we have set serveroutput to on).<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEif6uQII02EM7T7SWOVMGsyqAfvVBVAtCvmYDXajQHgQn8XpXCxGfFcQEJ6yi-vpNAj8hIvD6-jY75nEPTLkVlTZ5c2-SwVPvdXMioP4A0bA1P7CvOKNyToT81DzxEpKdHczS8BRyXvMCQG/s1600/05_output.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="297" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEif6uQII02EM7T7SWOVMGsyqAfvVBVAtCvmYDXajQHgQn8XpXCxGfFcQEJ6yi-vpNAj8hIvD6-jY75nEPTLkVlTZ5c2-SwVPvdXMioP4A0bA1P7CvOKNyToT81DzxEpKdHczS8BRyXvMCQG/s400/05_output.jpg" width="400" /></a></div>
<br />
Nothing new so far, I hope. Before we continue I just wanted to mention that as of Oracle11G we have the compound trigger feature.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjkc7PQoJiC5bsuEI1TKH5vrp_L7DYnYf1riyAvr_gXkp1cdFe2PChbDHIJrx8xDZ-uqJ-RyaWUUaNEzNqtv8yquvb0Nz2gcDGHqlWiu-i7YLRgX5TMJnk1XHbUGg9zghrxxCXxKTTF03Vv/s1600/06_compound.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="298" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjkc7PQoJiC5bsuEI1TKH5vrp_L7DYnYf1riyAvr_gXkp1cdFe2PChbDHIJrx8xDZ-uqJ-RyaWUUaNEzNqtv8yquvb0Nz2gcDGHqlWiu-i7YLRgX5TMJnk1XHbUGg9zghrxxCXxKTTF03Vv/s400/06_compound.jpg" width="400" /></a></div>
<br />
A compound trigger enables us to create the four update triggers above all in one go as follows:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi8UefqjsJYk_MaS44jI9WN_NPaHwKf565oQNVdxMJgwvD2_V6qNnt5t8jJQ_uQTqa5PEFHzXLpgyla2-Cy4j9S5Rxi5No8QJQXZTF3aHE_86KDR3OM8mKk8BTpNWCm20qZ3MZ66Bq38Hww/s1600/07_compound2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi8UefqjsJYk_MaS44jI9WN_NPaHwKf565oQNVdxMJgwvD2_V6qNnt5t8jJQ_uQTqa5PEFHzXLpgyla2-Cy4j9S5Rxi5No8QJQXZTF3aHE_86KDR3OM8mKk8BTpNWCm20qZ3MZ66Bq38Hww/s400/07_compound2.jpg" width="400" /></a></div>
<br />
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.<br />
<br />
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.<br />
<br />
Oracle DBMS offers us with many more triggers:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvRyJz0gQcuLdn2Jbo28aryn-WYP_y2p3j44NGnRzG1k7y1SN1dUq1RAUFsxX8Od-beoNp2-m5JCg2uNorf4BNQXl8cVFoCRKFIWNYcod5srygi90pCvedubb6SWRaoMgC61f2gkzP-MIk/s1600/08_outscope.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="298" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvRyJz0gQcuLdn2Jbo28aryn-WYP_y2p3j44NGnRzG1k7y1SN1dUq1RAUFsxX8Od-beoNp2-m5JCg2uNorf4BNQXl8cVFoCRKFIWNYcod5srygi90pCvedubb6SWRaoMgC61f2gkzP-MIk/s400/08_outscope.jpg" width="400" /></a></div>
<br />
All of which will not be the matter of subject for this blog.<br />
<br />
Stay tuned.<br />
<br />
<br />Toon Koppelaarshttp://www.blogger.com/profile/08153913435188725112noreply@blogger.com21tag:blogger.com,1999:blog-1339993448594009052.post-83351625401804199542011-11-22T00:58:00.001-08:002011-11-22T23:57:38.019-08:00Starting this blogSo I finally was able to find some time to start this blog. I mentioned before that I might do this <a href="http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:3868442800346504246">in the asktom discussion here</a>. This blog will be about database triggers. Oracle database triggers that is. I'm a strong advocate of database triggers. Now hold your horses... I have been using database triggers ever since Oracle7 in the early nineties brought them to us. But I've been using them for a very specific reason, and have been presenting about this ever since. I will blog about this in a very similar way as I blogged about <a href="http://thehelsinkideclaration.blogspot.com/2009/03/start-of-this-blog.html">TheHelsinkiDeclaration</a>, by documenting the presentation I have frequently given now at various Oracle related seminars and user group meetings.<br />
<br />
Let me present exhibit A, showing that I have been presenting about a particular use-case for using database triggers for quite some time now. Here's the front cover of the 1994 International Oracle User Week (that's how Oracle OpenWorld used to be called in the nineties) conference.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqy8NIok_1jPskyzf6PIYj_6Vaj_zBDAY_LMkmuR5GXTa27N5KvhMORx2ABWSj96f8ZkMQXPbAco1yUF9K4-QWjogns7cJ0mTHUOiAIBv2Dh-arO7MF05wthSAR_lPgnrAzJSmAHfeL_Ye/s1600/hot1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqy8NIok_1jPskyzf6PIYj_6Vaj_zBDAY_LMkmuR5GXTa27N5KvhMORx2ABWSj96f8ZkMQXPbAco1yUF9K4-QWjogns7cJ0mTHUOiAIBv2Dh-arO7MF05wthSAR_lPgnrAzJSmAHfeL_Ye/s320/hot1.jpg" width="226" /></a></div>
And here's a part of the contents.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg8ar3yc8MvvQdFRxyrbgt_MGc0jQ2o3nnWGCYGWReYTsyXe4uFcI1jyB6h-UwqDk0CTDGF3YlHBrq4vvtiKLCEV9dqd5WbjQDQyFrxpBQJIp3K2kS5-GqA_zbY0FijYnv2PdbD-fOJrxSy/s1600/hot2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg8ar3yc8MvvQdFRxyrbgt_MGc0jQ2o3nnWGCYGWReYTsyXe4uFcI1jyB6h-UwqDk0CTDGF3YlHBrq4vvtiKLCEV9dqd5WbjQDQyFrxpBQJIp3K2kS5-GqA_zbY0FijYnv2PdbD-fOJrxSy/s640/hot2.jpg" width="452" /></a></div>
<br />
Paper #10, "Business Rules: Classification and Implementation (...)". And as you can see I was employed by Oracle back then. Note that Steve (Feuerstein) was already presenting in those days too. Now some of you can already guess what my talk was about. The use-case for triggers that I'm advocating, that is. For those of you who don't, stay tuned, I will explain the use-case and my arguments for it, in the posts that will follow.<br />
<br />
So that was (almost) two centuries ago, business rules presentations by T(oon) K(oppelaars)...<br />
<br />
Fast forward to this millenium. Here's another TK on triggers in the database:<br />
<br />
From <a href="http://tkyte.blogspot.com/2006/09/classic-example-of-why-i-despise.html">http://tkyte.blogspot.com/2006/09/classic-example-of-why-i-despise.html </a><br />
"Triggers are so abused and so used inappropriately, I'd rather live without them."<br />
"There are no times triggers cannot be avoided. They are purely a convenience that is overused, abused, and improperly used."<br />
<br />
And from: <a href="http://tkyte.blogspot.com/2007/03/dreaded-others-then-null-strikes-again.html">http://tkyte.blogspot.com/2007/03/dreaded-others-then-null-strikes-again.html</a><br />
[on why he doesn't like triggers] "Because I hate being surprised or tricked. And triggers are all about trickery and surprises."<br />
<br />
The other "TK" of course is Tom Kyte from <a href="http://asktom.oracle.com/">asktom.oracle.com</a>. Since you are reading this blog, I"m sure Tom Kyte needs no further introduction. He makes no secret of the fact that he has strong reservations about the database feature called "triggers". And this opinion is wholeheartedly embraced by the PL/SQL community at large. The default response whenever a question on database triggers pops up on <a href="https://forums.oracle.com/forums/forum.jspa?forumID=75&start=0">PL/SQL OTN forum</a> is:<br />
<br />
"Triggers should be avoided as much as possible."<br />
"Don't use them, they are bad."<br />
"Triggers are considered harmful."<br />
<br />
Well excuse me, but I find this general consensus harmful.<br />
<br />
At this time I should disclose that I run a company called <a href="http://www.rulegen.com/">RuleGen</a>, which is also the name of a product that generates Oracle trigger code to implement business rules.<br />
<br />
A year ago I decided to present on why I find this general consensus harmful. And this resulted in a presentation at <a href="http://www.hotsos.com/sym11/sym_speakers_koppelaars.html">Hotsos 2011</a>. The title of this presentation is: "Triggers considered harmful, considered harmful". And that's not a typo, no. I'm using the "considered harmful" concept which was introduced by Edsger Dijkstra when he published a paper called "Goto statement considered harmful". And as you can read <a href="http://en.wikipedia.org/wiki/Considered_harmful">here</a>, it gave cause to quite some 'follow up' papers with the same "typo" in their titles.<br />
<br />
Recently I also delivered this presentation at the <a href="http://www.bgoug.org/">BGOUG</a>. And it has been submitted for presentation at the upcoming <a href="http://kscope12.com/">ODTUG</a>. I'll let you know if it got accepted, so you can attend ODTUG and hear a live version of this blog.<br />
<br />
So much for the introduction. Future posts we will go into the details of why I find triggers not harmful at all given they are used for a very specific use-case.<br />
<br />
<br />
<br />
<br />Toon Koppelaarshttp://www.blogger.com/profile/08153913435188725112noreply@blogger.com11