sql server programming: referenced row -- sql server programming

Re: referenced row

Steve Kass
12/17/2004 6:45:40 PM

I'm never happy when this happens to me, especially if
it's an airline ticket that has gone up a lot in 5 minutes,
but depending on what else is going on, it could be to
prevent people from creating a shopping cart on sale day
and then checking out months later when the actual price
is much higher. Refreshing prices when the customer
checks out might be a better option, but I'm sure the
real situation has more constraints than we see here.
(For example, shopping carts that can hold more
than one item.)

Emre - how about trying this:

CREATE TABLE Products (
ProductID INTEGER IDENTITY(0,1) NOT NULL,
UnitCost INTEGER NOT NULL,
price INTEGER,
CONSTRAINT uq_Products UNIQUE (ProductID),
CONSTRAINT PK_Products PRIMARY KEY (ProductID, price)
)
GO
CREATE TABLE ShoppingCart (
sopid INTEGER IDENTITY(0,1) NOT NULL,
ProductID INTEGER,
pro_price INTEGER,
CONSTRAINT PK_ShoppingCart PRIMARY KEY (sopid)
)
GO
ALTER TABLE ShoppingCart
ADD FOREIGN KEY (ProductID, pro_price)
REFERENCES Products (ProductID, price)
ON UPDATE CASCADE
GO

DROP TABLE ShoppingCart, Products

Steve Kass
Drew University

Steve Kass
Drew University

[quoted text, click to view]

Re: referenced row

David Portas
12/17/2004 9:58:07 PM

UPDATE ShoppingCart
SET pro_price =
(SELECT price
FROM Products
WHERE productid = ShoppingCart.productid)

--
David Portas
SQL Server MVP
--

Re: referenced row

David Portas
12/17/2004 10:11:11 PM

See the UPDATE I posted. But your requirement doesn't make much sense to me.
Are your customers really happy to have prices change after they have added
them to the cart? Shouldn't the price be populated at INSERT and then be
unchanged?

--
David Portas
SQL Server MVP
--

Re: referenced row

David Portas
12/17/2004 10:53:27 PM

Reference?

A trigger doesn't seem appropriate to me for the reason I explained in my
second post. Yes, a trigger on Products is one possibility:

UPDATE ShoppingCart
SET pro_price =
(SELECT price
FROM Inserted
WHERE productid = ShoppingCart.productid)
WHERE EXISTS
(SELECT *
FROM Inserted
WHERE productid = ShoppingCart.productid)

--
David Portas
SQL Server MVP
--

referenced row

Emre Guldogan
12/17/2004 11:45:53 PM

Hi , i need some help . My problem is referencing a column from another
tables column
let say these two are my db tables . I want pro_price in Products to be
taken from price in Products .
Whenever price in Products is updated , pro_price in Products is updated
too .
How can i do ?

CREATE TABLE Products (
ProductID INTEGER IDENTITY(0,1) NOT NULL,
UnitCost INTEGER NOT NULL,
price INTEGER,
CONSTRAINT PK_Products PRIMARY KEY (ProductID)
)
GO
CREATE TABLE ShoppingCart (
sopid INTEGER IDENTITY(0,1) NOT NULL,
ProductID INTEGER,
pro_price INTEGER,
CONSTRAINT PK_ShoppingCart PRIMARY KEY (sopid)
)
GO
ALTER TABLE ShoppingCart
ADD FOREIGN KEY (ProductID) REFERENCES Products (ProductID)
GO

Re: referenced row

Emre Guldogan
12/18/2004 12:04:44 AM

for performans reasons . This is not the only price that i have to calculate
..
Is there any way to do this ?
thanks

[quoted text, click to view]

Re: referenced row

hoz
12/18/2004 12:14:37 AM

Sir ,
this is not a reference so the solution you just offered does not solve the
problem .
Maybe someone would say put this it on a update trigger for Products , but
again it is not a reference
thanks

[quoted text, click to view]

Re: referenced row

David Portas
12/18/2004 3:47:53 PM

I like Steve's suggestion if denormalization is a requirement.

Emre mentioned that this was for "performance reasons" but didn't give any
details. If the volume is 1000 orders per day then maybe your table is small
enough that the redundant column won't harm performance but in the long run
I suggest that this approach is unlikely to scale well. As always, test,
test, test. :-)

--
David Portas
SQL Server MVP
--

Re: referenced row

hoz
12/18/2004 5:03:19 PM

Dear David ,
It is a requirement for us to keep customers shopping cart on db! , and when
they come back after one month price must be updated (if still exists).
Our system is a real time system , which gets about 1000 orders per a day .
it doesn't make much sense to me what you have offered [if u offer
something ]
Anyway , thanks for your answer .

Hasan Ozgur
Computer Engineer

[quoted text, click to view]

Re: referenced row

hoz
12/18/2004 5:06:56 PM

Dear Sir ,
We are not happy too but this is a must for us to do . You analysed what the
post means correctly.
Thanks for your answer

[quoted text, click to view]

Re: referenced row

hoz
12/18/2004 5:08:54 PM

I forgot to tell shopping card id is also kept at cookie not in a session ;)
maybe it would help you to analyse correctly

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.