asked on

SQL Server 2008: Auto increment column

HI!

I need some help how to increment a column (RevisionNumber) depending of ProductType.

So if I have [ProductType] 10 and 20 both with [RevisionNumber] 1, if I then I insert a new ProductType 10, then I like RevisionNumber to be 2, and the next time I insert a new ProductType 20 I like that RevisionNumber also to be 2, (not 3 if you use default Auto Increment)

So I only want to increment [RevisionNumber] with +1 from the number it already has, so if it has 4 I like it to be 5 regarding of what the Auto Increment has done on another product type.

Is this possible?

Raja Jegan R

Ok, Try using the below approach:

1. Create AFTER TRIGGER for INSERT statements in that particular table to update RevisionNumber column for a particular record.(Update with the Max value + 1 for the particulary ProductType)
2. Create a unique index on ProductType column and RevisionNumber column.

Guy Hengel [angelIII / a3]

this is possible, but not built-in.you might need to use either a procedure to handle the insert (and find out the highest previous revision number for the product_type, +1, or a trigger to do this ...what's your take ...

AWestEng

ASKER

The ProductType and RevisionNumber column have Primary keys on both.
And I think a trigger maybe best here.

Guy Hengel [angelIII / a3]

the issue is that you need another primary key, actually, to be working correctly. ..

anyhow, this trigger assumes that you insert "0" as RevisionNumber for "autoassignment"

create trigger on yourtable for insert 
as
begin
  update t
      set r.RevisionNumber =1 + isnull( ( Select max(x.RevisionNumber) from yourtable x where x.ProductType = i.ProductType ) , 0 )
    from yourtable t
   where t.RevisionNumber is null
      or t.RevisionNumber = 0  
end

Open in new window

AWestEng

ASKER

hmm I get this error

Msg 4104, Level 16, State 1, Procedure AutoIncrementRevisionNumber, Line 16
The multi-part identifier "r.RevisionNumber" could not be bound.

any tips?

AWestEng

ASKER

if i changed the "r" to "t" and the "i" to "x2, it worked to insert the trigger, is that correct then?

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AWestEng

ASKER

Perfect, thx man