Re: triggers and update count

Hello Alex,

The point about the result test is simply that when we are updating 
by primary key we should only update at most one record because the 
primary key is unique.  If it updates 0 records that means that the 
trick about adding optimistic fields to the where clause has failed.

Are you saying that in MS SQL if I update one record in a table, and 
that fires a database update trigger that updates two other records, 
then ps.executeUpdate returns 3, not 1?!!!   That would be weird -- 
you normally want to know how many rows your where clause matched, 
not details of indirectly fired events.

Before proceeding please write a trivial raw JDBC example that tests 
that, and send it to me.

Which JDBC driver are you using?

If you are right then we will need to an extra condition to the test 
that lets you say "Indirect updates count" or something.  Or maybe 
just remove the exception, unique indexes are generally quite unique.

Anthony

At 05:04 AM 17-08-07, Alex B wrote:

>Hi all,
>
>in a recent project we are attempting to make use of triggers to 
>increment snapshot ids (kept in separate tables), which will save 
>CPU cycles when querying for recent changes to the database.
>The reasons to use triggers over some implementation in the 
>businesslogic ( e.g. SimpleORM's pre-flush hooks) are several, e.g.
>a) one snapshot represents the state of several tables (i.e. several 
>triggers work on the same snapshot)
>b) manual data edits should still be safe and painless.
>
>The issue we have come accross with this approach is that when SORM 
>flushes a record to the database, the update count contains updates 
>to tables updated by the trigger (SQL Server) and the optimistric 
>lock check code (see below) in SRecordInstance.flush() will throw an 
>SException.InternalError since it is expecting the update count to be 1:
>   /// Execute the Query
>       int result = 0;
>       try {
>         result = ps.executeUpdate();
>       } catch (Exception rsex) {
>         throw new SException.JDBC(
>           "Executing " + qry + " for " + this, rsex);
>       }
>       if ( result != 1) {
>         if (optimisticFieldValues != null && result == 0)
>           throw new BrokenOptimisticLockException(this);
>         else
>           throw new SException.InternalError(
>             "Rows Updated " + result + " != 1 "  + this);
>       }
>
>Now the question:
>Why is the check done for result != 1 rather than "result not in {1,2,3}"?
>Simpleorm is built around primary keys and I am assuming that 
>SRecordInstance.flush() could never update more that one row 
>directly. If the update counter is indeed higher than 1, then only 
>due to - intended - sideeffects. What uninteded effects could this 
>code be trying to trap? Is it safe or would we miss nasty conditions 
>if we were to write the optimistic check code as:
>if ( result <= 0 || result >=3 ) {
>     // handle condition
>
>Thanks for any responses in advance,
>Alexander
>

EMail: [email protected]

+61 7 3369 4065 (Australian Eastern Standard Time)

If the model seems correct only because the numbers look right,
then why build the model in the first place?


 
Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/SimpleORM/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/SimpleORM/join
    (Yahoo! ID required)

<*> To change settings via email:
    mailto:[email protected] 
    mailto:[email protected]

<*> To unsubscribe from this group, send an email to:
    [email protected]

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/