How to create a trigger with auto increment

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to create a trigger with auto increment

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-28 : 15:18:46

Liriam writes "Hi,

I need to create the table below. The field "f1" must be auto incremented whenever a new row is inserted but I cannot use IDENTITY for "f1" because of some restrictions.

Where is the problem in my trigger?

-----------------------
create table TAB1
( f1 int primary key,
f2 varchar(20))

-----------------------

create trigger tr_t1 on TAB1
INSTEAD OF INSERT
AS

BEGIN
DECLARE @MAX INT
SET @MAX = 0

SELECT @MAX = MAX(F1) FROM TAB1

SET @MAX = @MAX + 1

UPDATE f
SET
i.f1 = @MAX
FROM
inserted i
END

Thanks in advance,

Liriam "

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-07-28 : 23:10:13

What are those restrictions? If you're using SQL 2K, you might find a workaround by using a computed column as your primary key, defined as an expression derived from an identity column.

Jonathan Boott, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources