December 3, 2002 at 9:45 am
I am trying to create a trigger that will automatically generate an increasing number for a field called ReceiptNumber, whenever that field is updated and it's value is null. I can get it to work for single updates, but I can't seem to figure out how to get it to work for updates that involve multiple rows. Here's what I have right now:
IF (Select ReceiptNumber From Inserted) is null
BEGIN
Update Batch Set ReceiptNumber = (SELECT ReceiptNumber from Config) FROM Inserted WHERE Batch.TransactionID = Inserted.TransactionID
Update Config Set ReceiptNumber = ReceiptNumber + 1
END
The Config table contains one column and one row that holds the next available receipt number that can be used. I know... try not to laugh too loudly at this. What else can I do? Should I use a table for storing the receipt number or is there a much better way? I can't use an autoincrement field, because I need to be able to change the receipt number when the record is "updated", not inserted.
Any help you can give to a SQL newbie is much appreciated.
December 6, 2002 at 8:00 am
This was removed by the editor as SPAM
December 6, 2002 at 9:51 am
You would be better off using and IDENTITY filed and setting the seed value and incremement of 1.
You are building the same thing here basically anyway except for your to work you then have to reference the TransactionID to apply and make an additional update plus the field has to be NULL anf if is part of an index you make two adjustments to the index.
However if say the TransactionID is an IDENTITY and thus you cannot have a second figure up what the difference is between TransactionID and the value the ReceiptNumber should be and create a computed column instead or use the transactionID as your ReceiptNumber.
If for someother reason consider putting the entire logic instead in an SP then try like this
CREATE TABLE config (RecAutoNum int NOT NULL)
GO
INSERT config (RecAutoNum) values (1)
GO
In your SP you retrieve the next value and set in this motion
DECLARE @RecAutoNum
UPDATE config SET @RecAutoNum = RecAutoNum, RecAutoNum = RecAutoNum + 1
INSERT batch (col1,col2,col3..., ReceiptNumber) VALUES(@yourcol1, @yourcol2, @yourcol3..., @RecAutoNum)
This mean one transaction to the table without a cursor.
If you do the same with a Trigger and have more than one insert at a time you will have to use a CURSOR to loop thru all the items in INSERTED and do the UPDATE config within the cursor as in my SP ex. and do an UPDATE on batch.
Or I haven't tried this yet. But you might could wrap the UPDATE config logic in an UDF (user-defined function) if using 2000 and return the value you set to each record in INSERTED for unpdate in batch.
There may be other options but these I see right off.
December 8, 2002 at 1:16 pm
A timestamp field is updated on every insert and update. You can't control its value but it would be incremented automatically and would be unique in the data base. You can convert it to a BIGINT value using Cast().
There would also be gaps in your numbers so they wouldn't be sequential.
December 8, 2002 at 6:31 pm
I would avoid timestamp for the fact that this implementation is not ANSI-SQL 92 comparable and as stated in BOL
quote:
A future release of Microsoft® SQL Server™ may modify the behavior of the Transact-SQL timestamp data type to align it with the behavior defined in the standard. At that time, the current timestamp data type will be replaced with a rowversion data type.
So instead think rowversion. Plus because timestamp will update every time you change the row it makes it a poor identity column.
December 8, 2002 at 10:12 pm
quote:
Or I haven't tried this yet. But you might could wrap the UPDATE config logic in an UDF (user-defined function) if using 2000 and return the value you set to each record in INSERTED for unpdate in batch.There may be other options but these I see right off.
This is what I was actually thinking of doing, but when I put the update code in the UDF, I got an error stating something like updates are not allowed in UDF's. Is this true, or am I just not getting the syntax right? If it's just me, would someone mind posting a simple function that could retrieve a receiptnumber from a config table and then update the receiptnumber to the next consecutive number (i.e. receiptnumber = receiptnumber + 1)? Thanks for your response, and I hope to hear some more good advice.
December 8, 2002 at 10:39 pm
Checked it out and nope it is not allowed. Like I said I hadn't tried it just a thought.
The only other thing that could do it would be to use a cursor to iterate thru the data of inserted one at the item at the time.
December 10, 2002 at 5:48 am
I did just find this out. You can create a extended stored procedure to wrap the code to do the update and give you the new value. Of course this a compiled C++ dll with proper stuff. But that is the only why at this point you could wrap into a function.
December 10, 2002 at 8:34 am
What we really need for this is an ANSI-compliant way to generating sequentially increaseing numbers. Anyone here on the appropriate committee? Or know who/where to submit suggestions?
December 16, 2002 at 12:05 am
Here is a technique to get the next sequence number to assigned to new records. Hopefully the three parts to the code become self-explanatory.
The updating the next sequence number to use on the Config table needs to be self contained, so that
/* == tables to illustrate == */
drop table Receipts
go
create table Receipts
(ReceiptNumber int not null,
CustomerId char(8) not null,
/* other fields */
auditid int,
constraint IX1Receipts primary key (ReceiptNumber, CustomerID) )
go
/* -- counter table -- */
create table Config (
ReceiptNumber int
)
go
/* -- populate it -- */
insert Config values (10001)
/* == create the trigger to insert the audit number == */
if exists (select id from sysobjects where id = object_id('tr_Receipt_Auditno')
and objectproperty(id,'IsTrigger') = 1 )
drop trigger tr_Receipt_Auditno
go
create trigger tr_Receipt_Auditno on Receipts
for insert
as
IF exists (Select ReceiptNumber From Inserted)
BEGIN
/*
records present, so get the number needing assignment
get the next sequence number and update for number used
*/
declare @nextaudit int, @incr int
select @incr = count(*) from inserted
Update Config Set @nextaudit = ReceiptNumber,
ReceiptNumber = ReceiptNumber + @incr
Update Receipts
Set auditid = @nextaudit,
@nextaudit = @nextaudit + 1
from Receipts join inserted
on Receipts.ReceiptNumber = inserted.ReceiptNumber
END
/* end of trigger */
go
/* == insert a few recrods in one hit == */
insert Receipts (ReceiptNumber, CustomerId)
select RN, CI from
(select 120 as RN, 'ABC' as CI
union all select 140, 'XYZ'
union all select 160, 'RET'
) A
/* == show the results == */
select * from Receipts
select * from Config
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply