James, I fail to see an issue using an AFTER INSERT in this example, so long as there is no UPDATE trigger, or the UPDATE trigger avoids changing the lot number.
As I have previously reported, there are very easy and clear methods to avoid endless loops in UPDATE triggers that UPDATE the row firing the trigger. We use them on three major inventories with no problems at all. Emmitt Dove Manager, DairyPak Business Systems Blue Ridge Paper Products, Inc. [EMAIL PROTECTED] [EMAIL PROTECTED] (203) 643-8022 -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of James Bentley Sent: Monday, January 14, 2008 12:16 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Lot numbering Emmitt, Amen to not using a computed column. However, Even using a TRIGGER can be tricky since the TRIGGER process is implemented via hidden BEFORE/AFTER READ ONLY cursors that provide WHERE CURRENT OF SYS_NEW/SYS_OLD syntax. In an INSERT trigger you cannot use WHERE CURRENT OF SYS_NEW syntax to alter the recored to be inserted. To accomplish adding the the lot number will require an UPDATE command issued from the AFTER INSERT trigger. Be careful in the UPDATE trigger not to cause and infinite loop. This process would be infinitely easier if BEFORE INSERT/BEFORE UPDATE triggers hidden cursors were updateable as major implementations such as DB2, MYSQL and ORACLE. --- Emmitt Dove <[EMAIL PROTECTED]> wrote: > Jan, > > > > One more thing - if you are going to use the date sequence in > your number, > I'd strongly advise against making the column a computed > column, since every > time you touch the row, even if it is a reload, the number > will change. A > trigger (or doing it in code before the INSERT) will avoid > this issue. > > > > Emmitt Dove > > Manager, DairyPak Business Systems > > Blue Ridge Paper Products, Inc. > > [EMAIL PROTECTED] > > [EMAIL PROTECTED] > > (203) 643-8022 > > > > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf > Of Emmitt Dove > Sent: Monday, January 14, 2008 10:05 AM > To: RBASE-L Mailing List > Subject: [RBASE-L] - Re: Lot numbering > > > > Jan, > > > > One more take for your consideration. > > > > In our case we have two inventories that we produce bar-coded > labels for. > In both cases we assign a 10-digit number as a unique > identifier for each > unit of inventory. The first two characters identify the > plant producing > the inventory. The third and fourth identify the specific > machine number > that produced the inventory. The last six are a > sequentially-assigned > number unique to the inventory (work-in-process or finished > goods.) That > number is stored on a record in a control table, and before > creating the > inventory record we retrieve it, increment it one and put it > back, all while > a lock is placed on the table. We then use the incremented > number in our > 10-digit sequence. If the 6-digit number hits 999999 we roll > it back to > 000001. In this way we will likely never have duplicates (the > most active > plant has been running this number sequence since 1992 and is > now up in the > 700,000's.) > > > > So our people can look at a load tag number and know which > plant and which > machine it was made on. The rest is just for uniqueness. > These tables do > not have autonumbers - they are not required. The 10-digit > number is type > TEXT (10) and keyed. > > > > Emmitt Dove > > Manager, DairyPak Business Systems > > Blue Ridge Paper Products, Inc. > > [EMAIL PROTECTED] > > [EMAIL PROTECTED] > > (203) 643-8022 > > > > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf > Of jan johansen > Sent: Monday, January 14, 2008 9:30 AM > To: RBASE-L Mailing List > Subject: [RBASE-L] - Re: Lot numbering > > > > Bob, Alastair, David, > > > > Thank you for your suggestions. > > The only reason for the YYYYMMDD sequence was to allow a form > of secondary > identification > > to a lot i.e. visual using human readable. > > I had initially proposed just using an autonumber but the > client requested a > form of secondary > > identification. The request was for the numbering sequence to > begin anew > every day > > hence > > today = 20080114001 > > tomorrow = 20080115001 > > so concatenating an autonumber to the date doesn't quite work > > > > > Another goal would be to keep the length the same > > since a 1 character 128code is narrower than a 6 character. > > So just using an autonumber starting at one would not quite > work unless it > could be > > formatted as 0000000001 (formatting an autonumber integer?) or > just using > > 10000000001 and go with that. But again what does that mean to > someone > looking at it. > > > > I'm begining to lean towards 20080114063030 (YYYYMMDDHHMMSS). > > This format remains consistent (as long as someone doesn't > screw with the > DATETIME settings > > and allows it to be stored as INTEGER vs TEXT. > > > > Enjoyed the discussion. > > > > Jan > > Jim Bentley American Celiac Society [EMAIL PROTECTED] tel: 1-504-737-3293 ____________________________________________________________________________ ________ Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs
