insert triggers urgent

[mkey]

Hi all,
i need to create an insert trigger where everytime there is an insert on the table i want the recordid column to go old value plus one.
my trigger look something like this but its not working properly!

CREATE TRIGGER TRGname ON table_name
FOR INSERT
AS
declare @id int
select @id = max(recordid) from table_name
update table_name set recordid = @id + 1

Any input will help!

 

[forecasting]

You dont need to generate your own record id. If you recordID as an identity field, SQL Server will do automatic increments.

 

[mkey]

well I knew that. But my requirement is such that i need to write a trigger instead of identity!

 

[chenthorn]

Please do not use a trigger to do this. An identity column would work better, and makes the trigger unnecesary.
see BOL.

 

[chenthorn]

try this:

CREATE TRIGGER TRGname ON table_name
FOR INSERT
AS
declare @old int,
@new int
select @old = recordid from inserted
select @new = @old+1
update table_name set recordid = @new where Recordid = @old

 

[forecasting]

Why do you need to insert the identity as a trigger?

 

[mkey]

I'm not sure why they want a trigger instead of identity.From what I understood was they are having some difficulty when accessing through another application.

Thank you for your help! I tried the trigger its still doesn't work!