| Downloads |
|---|
| 25912.zip |
I need a trigger that updates a character-based column in a System Numbers table with zero-prefixed, 8-digit incrementing values. For example, I want to assign the value 00000001 to the first inserted row, the value 00000002 to the second row, and so on. The trigger works fine on a one-row insert. But if I use the following statement:
INSERT INTO NewTable (x,y,z) SELECT x, y, z FROM OldTable WHERE <OldTable.Condition is True>
and I'm inserting more than one row, the only row that the trigger updates is the last row inserted. Listing 3 shows how the incrementing column value is calculated. I'm trying to avoid using a cursor that loops through each record of OldTable. Will SELECT INTO let me do this, or should I bite the bullet and use a cursor?
No, you don't have to use a cursor. Instead, you can use a set-based approach to solving the problem. Let's expand and explain your request and your code a little further. A table called Sysfile in your database contains a column named Next_Ach_Trans#. This column holds an integer sequence that generates counter-based, incrementing, formatted transaction IDs. Let's create the Sysfile table by using the following statement:
CREATE TABLE Sysfile ( Next_Ach_Trans# int NOT NULL PRIMARY KEY ) INSERT INTO Sysfile VALUES(0)
You have another table named Trans that contains a character column called TranID. Let's create this table by using the code in Listing 4. Upon an insert into Trans, the trigger's job is to update the TranID column in the Trans table's new rows with the next ID sequences, formatted as 8-digit numbers that contain leading zeros. The trigger needs to advance the sequence in Sysfile accordingly. However, your current trigger doesn't handle multirow inserts correctly. Before I suggest a way to revise your insert trigger, I'm going to show you an approach that might suit you better, depending on your business needs.
Listing 5 shows how you can add an IDENTITY column to the Trans table that will accommodate the auto-generation of sequence values and how to make the TranID column a computed column. The computed-column technique converts the new IDENTITY value to varchar, concatenates the result to a string of eight zeros, then extracts the rightmost eight characters. This process gives you the TranID value you're looking for and eliminates the need for the Sysfiles table and the Next_Ach_Trans# column. Note that in SQL Server 2000, you can create an index on a computed column, so this solution doesn't limit the performance of searches based on TranID. In SQL Server 7.0, you won't get good performance when you filter based on the TranID column, but you can always filter based on the IntTranID column, which you can index. The following code shows an example of a multirow insert:
INSERT INTO Trans2(ID, x, y, z) SELECT 1001, 100, 100, 100 UNION ALL SELECT 1004, 200, 200, 200 UNION ALL SELECT 1007, 300, 300, 300
Table 2 shows the output of a SELECT * query against the Trans2 table after the multirow insert.
If the solution that adds an IDENTITY column doesn't meet your needs (e.g., you use the same source sequence in other tables), you can fix your trigger so that it will handle multirow inserts correctly, as Listing 6, page 12, demonstrates. First, the trigger determines whether rows were affected, and if it finds no affected rows, it aborts. Next, the trigger issues an UPDATE statement that modifies the sequence in Sysfile by replacing its current value plus the number of rows affected by the insert (@rc). At the same time, the UPDATE stores the next sequence in @mintranid. Then, the trigger issues another UPDATE statement, this time against Trans, to store the formatted TranID values. The following pseudocode calculates the formatted TranID value:
@mintranid + position of the ID value in a sorted list of IDs FROM inserted
For example, if @mintranid is 100, the first ID is assigned 101, the second is assigned 102, and so on. The TranID value is formatted as an 8-digit string that contains leading zeros. Now try the two multirow inserts that Listing 7, page 12, shows. Table 3, page 12, shows the output of a SELECT * query against the Trans table after the inserts have taken place.
Note that if you use multirow inserts with large numbers of rows, the UPDATE statement renders poor performance because the inserted table isn't (and can't be) indexed. The subquery fully scans the inserted table once for each new row. To avoid this problem, you can create an indexed temporary table that includes an IDENTITY column (using a seed of 0) and insert into that temporary table all the IDs from the inserted table. You can then match the IDs from the temporary table and Trans and update the TranID column with the current minimum sequence plus the IDENTITY value. To implement this solution, replace only the last UPDATE statement in Listing 6's trigger with all the code in Listing 8.
CORRECTION:
In "Returning a List and a Range Count," May 2002, page 16, the first five lines of Listing 9 should have read:
SELECT m AS \[month\] COUNT(monthfrom) AS cnt_months FROM MonthRanges AS MR RIGHT OUTER JOIN
We're sorry for any inconvenience this error might have caused.
