> This is really neat. Could you possibly digress on how you came to > find that this would work, underlying principle or somesuch. How far can we > push this?
> >> RULES 'Receipt incorrect' FOR composition SUCCEEDS + > >> WHERE (100 -Weight_Percentage) >= + > >> (SELECT SUM(Weight_Percentage) FROM composition T1 + > >> WHERE T1.Receipt = composition.Receipt AND + > >> T1.CompID <> Composition.CompID) JM did most of the work, coming up with a rule that summed the weight_percentage in the "exisiting" rows in the table and compared it to the weight percentage "available" (that is, 100 less the percentage in the current row being checked). The only problem was devising a rule that would work both on initial inserts (when the row being checked has not yet been stored in the table) and on updates (where the row being checked is in the table, but might have an old value for weight_percentage if that field was changed in this update). In order to have a reliable rule, you need to get a consistent result from the SUM, not "sometimes the sum will include the current row and sometimes not". Since there is no way within the rule to flush the insert or updated value to the table (the equivalent of a SAVEROW) you can't produce a situation that consistently returns the total result including the new value (and you wouldn't want to anyway, since the purpose of the rule is to STOP the insert or update). However, you can design the rule to consistently EXCLUDE the value in question, but only if you can uniquely identify the row involved. Therefore I added a PK to the table (necessary for unique row identification) and expanded the WHERE clause in the second SELECT to exclude the current row. As far as general principles, I guess the one to draw is ALWAYS have a primary key on every table. Also, when designing any code that looks at data during a change to a row (Rule, Trigger, or EEP) and that looks at a set of rows in the table that might include the row being changed, make sure you eliminate the current row from the query into the table, since the correct information will not be found. As far as how far you can push this, you can go a lot farther. By using correlated SELECTS with EXISTS, for instance, you can do some really cool things. I'm constantly finding cases in my own code where I can go back and remove a cursor loop or series of views with a single SELECT statement. -- Larry _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
