 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / General DB Topics / DB Theory / August 2008SQL BEFORE puzzle
This is not a theory question by any means, but perhaps there is someone hanging out here who is able to speak with authority on SQL. We are trying to figure out the justification for why a BEFORE trigger should be prevented from doing any updates. The wording we're looking at from the standard is: "If the trigger is a BEFORE trigger and if, before the completion of the execution of any <SQL procedure statement> simply contained in triggered SQL statements, an attempt is made to execute an SQL-data change statement or an SQL-invoked routine that possibly modifies SQL-data, then an exception condition is raised: prohibited statement encountered during trigger execution." Obviously one shouldn't expect to be able to update a row that doesn't exist yet, but why prevent all updates (to other rows, other tables)?  SignatureRoy > This is not a theory question by any means, but perhaps there is someone > hanging out here who is able to speak with authority on SQL. We are > trying to figure out the justification for why a BEFORE trigger should > be prevented from doing any updates. The wording we're looking at from > the standard is: On a pure SQL perspective, I am going to play devil's advocate... The only justification I can see at midnight is that a rollback on transaction triggering the trigger would and should cause cascade rollbacks on subsequent tables to preserve integrity at (SQL) database level. Appart from the performance nightmare it would create on direct image systems, it would be necessary to *not* commit changes to subsequent tables before the main commit would occur. Somebody accessing the table modified may get wrong results if the main commit is rolled back or commited *after* the subsequent commits are committed. In any case, the safe way would force the subsequent commits to wait for the main transaction block to commit. On large transaction blocks that would be an obvious problem. Which is why in the first place an modification could be dangerous. Hope this helps... >> This is not a theory question by any means, but perhaps there is someone >> hanging out here who is able to speak with authority on SQL. We are [quoted text clipped - 17 lines] > > Hope this helps... I'm afraid it didn't. Based on your response I wonder if I have a very fundamental misunderstanding. I had assumed that the triggered updates would execute in the context of the same transaction as whatever update caused the trigger to execute. Is that (necessarily) wrong? Do the updates by triggers occur in a seperate transaction? SignatureRoy > >> This is not a theory question by any means, but perhaps there is someone > >> hanging out here who is able to speak with authority on SQL. We are [quoted text clipped - 25 lines] > the trigger to execute. Is that (necessarily) wrong? Do the updates by > triggers occur in a seperate transaction? Some DBMS do open that possibility by creating separate transactional context. A lot of DBMS's also make heavy use of dirty reads which make the principle somehow moot. My two cents... > Roy > This is not a theory question by any means, but perhaps there is someone > hanging out here who is able to speak with authority on SQL. We are [quoted text clipped - 11 lines] > Obviously one shouldn't expect to be able to update a row that doesn't > exist yet, but why prevent all updates (to other rows, other tables)? Not all implementations adhere to this restriction. For example, Oracle 10g's before-row and before statement triggers permit DML when they shouldn't while their after-row and after-statement don't when they should. I would think that the restriction is supposed to prevent something like: update T .... beforeT: update T .... beforeT: update T .... beforeT: update T .... ...and so on.... ...and so on.... ...and so on.... where the pending updates would pile up. Note that: update T .... afterT: update T .... afterT: update T .... afterT: update T .... ...and so on.... ...and so on.... ...and so on.... wouldn't cause the updates to pile up (even though the transaction controlling them would remain outstanding) because instead of being held waiting to be applied to the table as in the case of before triggers, they would have already been applied before each subsequent afterT is executed, making the recursive afterT more like just an iteration of updates within a transaction. >> This is not a theory question by any means, but perhaps there is someone >> hanging out here who is able to speak with authority on SQL. We are [quoted text clipped - 15 lines] > 10g's before-row and before statement triggers permit DML when they > shouldn't while their after-row and after-statement don't when they should. I am not overly interested in specific implementations at the moment, although I will probably come back to that topic later. I am more interested in why the standard would disallow update DML when a BEFORE trigger is fired. What is it about such updates that would result in inconsistent/undesirable behaviour versus the SQL ideal? > I would think that the restriction is supposed to prevent something like: > [quoted text clipped - 7 lines] > > where the pending updates would pile up. Fair enough, but why would the SQL standard care about that? Why, in fact, would an implementation prefer to prevent all updates instead of merely imposing some reasonable/configurable limit on the number of "piled up" updates? I am going to have to do more reading I can see. The most plausible conjecture I've come up with so far is that the standard either prescribes that any triggered BEFORE actions execute in a different transaction than the triggering update, or it is insufficiently precise about that, so the only safe option is to ban updates as a result of BEFORE triggers. SignatureRoy >>> This is not a theory question by any means, but perhaps there is someone >>> hanging out here who is able to speak with authority on SQL. We are [quoted text clipped - 39 lines] > merely imposing some reasonable/configurable limit on the number of > "piled up" updates? Expecting a committee to come up with something sensible correlates closely with one defnition of insanity: doing the same thing over and over and expecting different results. > I am going to have to do more reading I can see. The most plausible > conjecture I've come up with so far is that the standard either > prescribes that any triggered BEFORE actions execute in a different > transaction than the triggering update, or it is insufficiently precise > about that, so the only safe option is to ban updates as a result of > BEFORE triggers. This was the reason given in, Cochrane, R., Pirahesh, H. and Mattos, N. M. Integrating Triggers and Declarative Constraints in SQL Database Sytems. In: Proceedings of the 22nd VLDB Conference. Mumbai, India. Morgan Kaufmann, 1996. which I believe heavily influenced the SQL Standard on triggers. .... > I would think that the restriction is supposed to prevent something like: > [quoted text clipped - 22 lines] > making the recursive afterT more like just an iteration of updates within > a transaction. > This is not a theory question by any means, but perhaps there is someone > hanging out here who is able to speak with authority on SQL. We are [quoted text clipped - 11 lines] > Obviously one shouldn't expect to be able to update a row that doesn't > exist yet, but why prevent all updates (to other rows, other tables)? I'm not speaking with authority here, but I'll offer a guess: What if a given event triggers more than one BEFORE trigger? Now, there's a problem if one of the triggers is allowed to do updates, and the other trigger is executed in the context of the same transaction, but later, the second trigger doesn't actually see the BEFORE state of the database. >> This is not a theory question by any means, but perhaps there is someone >> hanging out here who is able to speak with authority on SQL. We are [quoted text clipped - 19 lines] > but later, the second trigger doesn't actually see the BEFORE state of the > database. That's a plausible-sounding guess. I'll have to think about it bit more before I say you must be right, but the undefined sequence of trigger executions would make doing updates risky... SignatureRoy > I'm not speaking with authority here, but I'll offer a guess: > [quoted text clipped - 3 lines] > but later, the second trigger doesn't actually see the BEFORE state of the > database. But you have the same issue with AFTER triggers. If an update fires multiple AFTER triggers, and some alter (other table) data and others read the same, the result is indeterminate if there's no way to somehow order the trigger firing. The answer with after triggers is to simply state that such things produce indeterminate results. I haven't been able to come up with an equivalent scenario for BEFORE triggers that can't be dealt with in the same way. On Aug 1, 7:47 am, "Walter Mitty" <wami...@verizon.net> wrote: quote: > I'm not speaking with authority here, but I'll offer a guess: > [quoted text clipped - 4 lines] > but later, the second trigger doesn't actually see the BEFORE state of the > database. But you have the same issue with AFTER triggers. If an update fires multiple AFTER triggers, and some alter (other table) data and others read the same, the result is indeterminate if there's no way to somehow order the trigger firing. The answer with after triggers is to simply state that such things produce indeterminate results. I haven't been able to come up with an equivalent scenario for BEFORE triggers that can't be dealt with in the same way. end quote. I contend that the issue isn't the same with AFTER triggers, although I see the problem with indeterminate serialization that you raise. What led me to question allowing some updates before a BEFORE trigger runs was not indeterminancy and serialization, but the fact that a BEFORE trigger can presumably rely on the data its looking at as being identical to what the data looked like before the event that set off the trigger. The second BEFORE trigger isn't seeing the BEFORE state anymore. AFTER triggers expect to look at data that's been updated by the original action, and therefore might not be disrupted by running after another AFTER trigger. But your comment makes me wonder.... Is there an issue in not letting an AFTER trigger run last? > On Aug 1, 7:47 am, "Walter Mitty" <wami...@verizon.net> wrote: > [quoted text clipped - 38 lines] > But your comment makes me wonder.... Is there an issue in not letting an > AFTER trigger run last? I would expect both the BEFORE and AFTER triggers to have a view of both the before and after images of the data. I don't really see the need for before and after triggers. Perhaps I am just missing it. Was it perhaps a case where someone intended the BEFORE trigger to act as a read-only constraint that could halt the update before it happened and the AFTER trigger as a way to cascade changes? > I would expect both the BEFORE and AFTER triggers to have a view of both > the before and after images of the data. I don't really see the need for > before and after triggers. Perhaps I am just missing it. I think it's largely for practical reasons. An AFTER trigger operates on a row that has already been physically placed somewhere in the table/index. If the trigger wants to alter a key value, the row probably has to move, and you end up with physical storage issues. BEFORE triggers avoid this, so they are preferred if part of the action is to calculate or update primary or secondary key columns. Of course this is all implementation dependent, but I suspect most implementations would work that way. >>I would expect both the BEFORE and AFTER triggers to have a view of both >>the before and after images of the data. I don't really see the need for [quoted text clipped - 11 lines] > Of course this is all implementation dependent, but > I suspect most implementations would work that way. Given that a rollback could undo the work, I would expect only the log to reflect updates in any case so I am not sure what the practical benefit would be even with the above considerations. > > I would expect both the BEFORE and AFTER triggers to have a view of both > > the before and after images of the data. I don't really see the need for > > before and after triggers. Perhaps I am just missing it. > I think it's largely for practical reasons. > An AFTER trigger operates on a row that has already been [quoted text clipped - 7 lines] > Of course this is all implementation dependent, but > I suspect most implementations would work that way. The AFTER trigger is a mechanism that can allow non database related operations to be performed independently from the context of a db engine. One may for instance add a line in order table and trigger the transfer of a file to an FTP server. In the absolute they should not be needed but dbms's support for workflow operations is very poor. Hope this helps. > > > I would expect both the BEFORE and AFTER triggers to have a view of both > > > the before and after images of the data. I don't really see the need for [quoted text clipped - 16 lines] > the transfer of a file to an FTP server. In the absolute they should > not be needed but dbms's support for workflow operations is very poor. AFAIK an AFTER trigger is normally called synchronously by the thread doing the transaction and before it commits or rolls back. There are two reasons to be wary of interacting with an external system in the way you describe: Firstly because most generally the transaction can actually roll back it may create some inconsistency (ie telling a remote system that something happened when it actually did not). Secondly, the original transaction will be made to block while these additional time consuming activities (like FTP) are being performed – therefore locks won’t be released quickly and concurrency will be badly affected. Note as well that sending a file using FTP would normally only need read access to the local DB so doing this within a mutative transaction (ie from an AFTER trigger) would lose the benefits of MVCC. It is generally better to use a separate (and therefore asynchronous thread) that uses read only transactions to send changes to another system. Often sequence numbers or time stamps can be used to ensure it only sends new content. This can often be made resilient to inconsistency problems if the receiver records where it is up to and this information is sent to the sender as part of their initial handshake in the communication protocol. This actually avoids the need for distributed transactions (ie multiphase commit). A very useful technique is for an AFTER trigger to signal an asynchronous thread that is sleeping on a waitable object such as a semaphore. > This is not a theory question by any means, but perhaps there is someone > hanging out here who is able to speak with authority on SQL. We are [quoted text clipped - 14 lines] > -- > Roy Roy, I won't claim authority but I can't find the sentence you refer to in SQL:2003 nor the latest draft of SQL:2006+ that I have (which is probably not the latest). I haven't looked in the original SQL:1999 standard but I did look up in SQL-99 Complete from Gulutzan & Pelzer and didn't see it either. What are you working from? Eric |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.