einhorn@triton.unm.edu (E Drew Einhorn ADV.SCI.Inc) (11/22/90)
Hello, I am trying to figure out how to write a sybase trigger to assign a unique sequence number to rows added to a table. This sequence number is to be used as the primary key for the table. So far the best I have been able to come up with is a technique that only allows rows to be added to the table one at a time. This technique reserves the value 0 with the meaning "not yet assigned". When the insert trigger detects the insertion of a row with seqno of 0 it increments the value of the entry in the table which keeps track last seqno assigned and updates the new row replacing the zero with the new seqno. The code performing the insert is responsible for the begin/commit transaction commands. Several problems arise when I try to generalize this to allow multiple rows to be inserted at one time. First I have to remove the unique specification from the index on seqno that prevents any erroneous code from screwing up the primary key. This code either has to be replaced with less efficient code in the trigger (or I have to have unjustified faith in my infallibility - Oh Well). I would need a FOREACH statement to step thru the rows assigning seqnos. Unfortuneately FOREACH is not included in Transact-SQL. I also have problems identifying the row to recieve the new seqno Oracle's ROWNUM or ROWID pseudo-columns would help out here. Hmm. With ROWNUM and ROWID I could get by without the FOREACH statement. -- einhorn@triton.unm.edu