November 21, 2006 at 1:46 pm
I don't know if this is a strange question or not, either way I would appreciate some advice.
I have a customer table which is a typical setup of:
customer no, customer name, address, site id, etc
Then I have another table that is used for customers with more than one site. This is basically the same as above but linked on customer no with what I would describe as its parent. Customer no is the primary field.
I then have a third table (this is when the problem arises) that is an asset register. What I want to achieve is assets (which are many) assigned to each site, linked to the site id. I would like to know if I could have the site id automatically generated so it is unique to the customer. For example, customer A that has three sites would have the main site as 1, and the branch offices as 2 & 3 respectively. Then with customer B they could use the same numbers as the sites are interlinked to the parent. Assets also have to be numbered 1,2,3...and so on for each customer. i.e. not global numbers.
I hope this makes sense and I look forward to hearing from someone soon.
November 21, 2006 at 2:29 pm
Well, first of all, you'll need (or want) a unique index on Customer No and Site ID in your thrid table. You could then either have the INSERT trigger determine the next available Site ID for that Customer No or if you are using a stored procedure for all INSERTs into this table, have it determine the next sequence number for your Site ID.
November 22, 2006 at 3:26 am
Sounds fine in theory as that is what I have read on the web already. I would appreciate it if you could give me some example code.
Thanks in advance for your help.
November 22, 2006 at 5:29 am
an alternatibe to the trigger solution is using a scalar function
create function NextSiteId(@custid varchar(4))
returns int
as
begin
declare @siteid int
if exists (select siteid from assettable where custid = @custid)
begin
select @siteid = max(siteid) + 1
from assettable where custid = @custid
end
else
begin
select @siteid = 1
end
return (@siteid)
end;
insert into assettable
(custid,siteid,.. blah blah)
values
(@custid,dbo.NextSiteId(@custid),... blah blah)
November 22, 2006 at 5:38 am
Just be warned that this approach does not guarantee unique values, it is possible for multiple users to get the same value, I think Itzik Ben Gan wrote some stuff about creating ID's.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
November 22, 2006 at 8:06 am
This version should be fairly robust. This will prevent multiple users from getting the same number, but it will create locking problems if you have a lot of concurrent users. With proper indexing this should only lock the index pages for a single customer for the duration of the insert, but if a customer has thousands of assets it could potentially escalate into a table lock. If you want to guarantee unique sequential numbers for each customer for a large number of concurrent users, you have my sympathy.
INSERT
INTO AssetTable (...)
SELECT @custid, MAX(AssetID) + 1, ...
FROM (
SELECT AssetID FROM AssetTable WITH(UPDLOCK) WHERE CustID = @custid
UNION ALL SELECT 0
) x
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply