homepage Welcome to WebmasterWorld Guest from 54.226.80.196
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Creating a ref coloumn in a sub table
SQL Server
Red_Eye




msg:3765304
 12:17 pm on Oct 14, 2008 (gmt 0)

I have 2 tables.

Orders

OrderID
[other columns etc.]

OrderParts
PartID,
OrderID,
Ref,
[other columns etc]

on the order parts column I would like to create a ref field for the orders parts table so the data in the order parts table would look like this.

PartID OrderID Ref
1......43.......1
2......43.......2
3......56.......1
4......56.......2
5......56.......3

Then when I get data from the datatable I can get order by the ref column.

Currently when I insert a new part row I use the following store procedure

DECLARE @Ref int

SELECT @Ref=MAX(Ref) FROM OrderParts WHERE OrderID=@OrderID

IF @Ref IS NULL
@Ref=1

INSERT INTO OrderParts (OrderID, ref [other columns]) VALUES (@OrderID, @Ref, [other columns])

this gets the data in.

But if a row is deleted I need to rebuild the ref column for that order id.

My question; is there a better way to do this? should I be using triggers to handle something like this.

Any help would be greatly apprciated

[edited by: Red_Eye at 12:22 pm (utc) on Oct. 14, 2008]

 

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved