Auto Increment Number in Insert Trigger

[bustercoder]

Hello,
Is it possible to auto increment a number in an insert trigger. For instance:
ALTER TRIGGER [dbo].[trig_insertFreightLine] ON dbo.Test_Shipments_Import
FOR INSERT, UPDATE, DELETE
AS
if exists (select * from inserted)
begin
insert SOP10102
select distinct 3,ORDER_ID,102,7,'',83,0,0,FREIGHT_AMOUNT,FREIGHT_AMOUNT,2999,'',0
from inserted
end
The "102" value you see in the above trigger needs to be incremented each time the trigger fires. Is this possible, and if so, could someone please show me how?

Thanks,
Buster

 

[ESquared]

there's a means in SQL server to do that already, it's called an identity column.

CREATE TABLE Blah(AutoID int identity(1,1), Value varchar(10))

INSERT Blah SELECT 'hello'

select * from Blah

if you have a table that already has numbers in it and you want to convert it to identity, you have to do something like this. Assuming the table is named Blah:

sp_rename 'Blah', 'BlahTemp' --any referential constraints have to be dropped and then recreated later

create table Blah () -- use same definition as original table only change definition of autoincrementing column to be int identity(1,1).

set identity_insert Blah ON
Insert Blah(partialcolumnlist, autoincrementingcolumn, restofcolumns) --all column names must be listed
select partialcolumnlist, nonautoincrementingcolumn, restofcolumns from BlahTemp --exclude
set identity_insert Blah OFF

-- re-add referential constraints.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[bustercoder]

The problem is this field is part of a composite key field in a financial table, so I don't have the luxury of being able to alter the identity insert mechanism, which is a line sequence number already starting with 16384 and incrementing by 16384 (16384, 32768, 49152, 65536) and so forth. So, can you show me a way I can do this myself? Like starting with a random number like 100 and incrementing by 1 with each insert?

 

[bustercoder]

Also, this field is not an identity (yes) field, so I can auto increment it myself, but I cannot change it to be an auto incrementing field as it would affect other default records being inserted as transactions. Hope that makes sense.

 

[ESquared]

Well, there are problems with the database design, but I imagine you aren't looking to correct those now and just want to make it work. So, saying that this is against my best judgment...

Wait, you showed 102 above. What's this 16384 stuff? Is this number incrementing across the whole table? Or do different rows have the same value, distinguished by some other column? If so, how does your trigger know what value to increment from?

create table MyAutoSeries(LastID bigint NOT NULL)

CREATE PROC GetNextSeriesID @NextID bigint OUTPUT
AS
UPDATE MyAutoSeries SET @NextID = LastID = LastID + 1 --confirm syntax, please
GO

ALTER TRIGGER [dbo].[trig_insertFreightLine] ON dbo.Test_Shipments_Import 
FOR INSERT, UPDATE, DELETE
AS 
            if exists (select * from inserted)
            begin
                DECLARE @NextID bigint
                EXEC GetNextSeriesID @NextID OUTPUT

                insert  SOP10102 
                select    distinct 3,ORDER_ID,@NextID,7,'',83,0,0,FREIGHT_AMOUNT,FREIGHT_AMOUNT,2999,'',0
                from     inserted
            end

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]