Complexx AutoIncrment colum

Hi,

I have a situation where I have to maintain auto incremental values based on values of other colum. Sample table is given below.

colA

colB

colC

1

1

1

1

1

2

1

1

3

1

1

4

1

2

1

1

2

2

1

2

3

2

1

1

2

1

2

2

1

3

Now in above table I want to autoincrement value of colC on the basis of Values of colA+colB. Is there any way I can set this fuctionalty directky on table schema.

Nilkkanth Desai

There is no way to create this kind of functionality on the table schema.

You could use a TRIGGER.

|||

You can use computed column for ColC.

Thanks

Naras.

|||

Hello Narasimhan Jayachandran ,

Thanks for your reply. I tried to findout samples on computed columns but I did not find any. Can u give me sample on this issue. or give me more details link if any on this topic. Your sample code will be more helpful for me to solve this issue.

Thank you,

Nilkanth Desai

|||

It is not good idea to use the computed columns here.. (in other words you can't achive the result using the computed columns, since your computed colum is non-deterministic).

The best approach may be on the trigger or you can use new feature OUTPUT.

dafafadfa

Using OUTPUT:

Create table ABCTable
(C1 int, C2 Int, C3 INT)

Go

Begin Tran;
Begin Try

Declare @.Inserted Table(C1 int, C2 int);

Insert Into ABCTable(c1,C2)
OUTPUT Inserted.C1,Inserted.C2 Into @.Inserted
values (1,1)

Update ABCTable
Set
C3=Isnull(NewC3,1)
From
(
Select
Max(C3)+ 1 NewC3,
A.C1,
A.C2
From
ABCTable A
Join @.Inserted B On A.C1= B.C1 and A.c2=B.C2
Group By
A.C1, A.C2
)as NewC3
Where
ABCTable.C1 = NewC3.C1
And ABCTable.C2 = NewC3.C2
And C3 is NULL

IF @.@.ERROR = 0
Commit Tran
Else
Rollback Tran

End Try

Begin Catch
Rollback
End Catch

|||

Hi Nilkanth,

Computed column :

Example : create table tbcc ( c1 int, c2 int, c3 as (c1+c2))

insert tbcc (c1,c2) values (1,2)

select * from tbcc

c1 c2 c3
-- -- --
1 2 3

More details, refer Books on line under Create table

--
CREATE TABLE
[ database_name.[ owner ] . | owner. ] table_name
( { < column_definition >
| column_name AS computed_column_expression
| < table_constraint > ::= [ CONSTRAINT constraint_name ] }

| [ { PRIMARY KEY | UNIQUE } [ ,...n ]
) ..............

--

Thanks

Naras.

|||

Hi Narah,

Thanks for am alternative with sample code. It works.

Nilkanth

|||

Another question:

What is done if a row is deleted; are the rows supposed to auto-decrement?

|||

Hi,

It seems for update also it require one more trigger. Overall Triggers are costly in terms of resource utilisation. As this is my main trnsaction trble which handles 98% of traffic of the system I think triggers will be more heavy solution. Microsoft shall provide some mechenism in future release products as this kind of situation is mostlikely faced by most of the database developers. If we can find any schema based solution it will be light & easy to deploy.

Anyway bu this time this is the only solution I can use. If anyone finds more better solution then this please update this blog.

Nilkanth Desai