Sql-server – How to add an automatically-populated custom sequence number

sql serversql-server-2005

I have table something like this:

CREATE TABLE [dbo].[ado_test_table](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [tip] [varchar](10) NOT NULL,
    [datum] [datetime] NOT NULL,
    [doc_number] [int] NULL
) ON [PRIMARY]

I want column doc_number have sequential numbers for each tip. To be clear, I want to automatically or default or use a computed column with a function like this:

create FUNCTION [dbo].[fn_test_number]
(
     @tip varchar(10)
    ,@datum datetime
)
RETURNS int
AS
BEGIN
    DECLARE @number int

    select
        @number=    max(doc_number)+1
    from ado_test_table
    where
        year(datum) = year(@datum)
        and tip = @tip

    set @number=isnull(@number,1);

    return @number
END

In SQL Server 2005, can I have a column which will automatically have values? Should this be done by computed column, default (constraint) or trigger?

Best Answer

Create a trigger that on INSERT will populate that filed.

You cannot have it as a calculated column, as latter cannot refer to any tables.

Related Question