|
|
Posted by Khafancoder on 05/26/07 09:54
> But of course, I don't know what your real case is like. Maybe you are
> only using stores, products and parts for the sake of the example?
exactly!
so i can't change the design and also i can't disable identity
insertion !!
i'm trying to take an alternative way, something lik this :
--disable check constrains
INSERT INTO Parts
(ProductId, PartName, PartDesc)
SELECT
dbo.CopyProduct(ProductId, @DestinationStoreId) , PartName, PartDesc
FROM Parts WHERE StoreId=@StoreId
--enable check constrains
and CopyProduct functions is supposed to copy requested ProductItem
and return it's Id,
but i can't do that in UDF !!
CREATE FUNCTION CopyProduct(@ProductId bigint, @DestinationStoreId
bigint) RETURNS bigint
AS
BEGIN
INSERT INTO Products (StoreId, ProductName)
SELECT @DestinationStoreId, ProductName FROM Products WHERE
ProductId=@ProductId
RETURN SCOPE_IDENTITY()
END
i think it should solve the problem, but because of sqlserver
restriction i can't do that in a function !!
any idea ?
On May 26, 11:50 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Khafancoder (khafanco...@gmail.com) writes:
> > because copied products or parts info may needed to be changed by each
> > owner.
> > so, i will consider disabling identity insertion.
>
> I still don't think this is a good design. Reasonably, there are
> inherit properties with a product that does not depend on the store.
>
> I think you need a new table ProductStores:
>
> CREATE TABLE StoreProducts(StoreId bigint NOT NULL,
> ProductId bigint NOT NULL,
> CONSTRAINT pk_ProductStores PRIMARY KEY(StoreId,ProductId))
>
> This table can then be augmented with columns that the store owner can
> set as he pleases. If needed, you could also have a StoreProductParts,
> with (StoreId, ProductId, PartNo) as key and with (StoreId, ProductId)
> as foreign key to StoreProducts and (ProductId, PartNo) as foreign key
> to Parts. But it seems funny to me that the same product would have
> different parts in different stores.
>
> But of course, I don't know what your real case is like. Maybe you are
> only using stores, products and parts for the sake of the example?
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|