SEO

#1 (**permalink**) 02-29-2008, 03:16 AM

hello group,

i want to denormalize data in a temporary table for a second
business-system which cant read related datas

what i have:
table Partner: (Id_Partner, NamePartner, ...)
table PartnerProduct: (Id_PartnerProduct, NamePartnerProduct, ...)
table Partner2PartnerProduct: (Id, Id_Partner, Id_PartnerProduct)

Partner to PartnerProduct ist n:m relation (thats why i need
Partner2PartnerProduct)

and the temporary table
TempPartner: (Id_Partner, NamePartnerProducts)
whereas NamePartnerProducts should contains a comma separated list of
the PartnerProduct-Names

what i need:
a trigger or combination of trigger and SP which
operate like the following:
if on Partner2PartnerProduct is a Insert or Update made, then Delete
the row in TempPartner which has the Id of the Partner, and then
create a new row which contains all n:m joined PartnerProduct-Names in
it.

i hope the intention and problem are clear.

thanx for answers,
hans

#2 (**permalink**) 02-29-2008, 03:16 AM

"Hans Bampel" <web-accounts@gmx.de> wrote in message
news:73ce18be.0406170455.1790ac7b@posting.google.c om...
> hello group,
>
> i want to denormalize data in a temporary table for a second
> business-system which cant read related datas
>
> what i have:
> table Partner: (Id_Partner, NamePartner, ...)
> table PartnerProduct: (Id_PartnerProduct, NamePartnerProduct, ...)
> table Partner2PartnerProduct: (Id, Id_Partner, Id_PartnerProduct)
>
> Partner to PartnerProduct ist n:m relation (thats why i need
> Partner2PartnerProduct)
>
> and the temporary table
> TempPartner: (Id_Partner, NamePartnerProducts)
> whereas NamePartnerProducts should contains a comma separated list of
> the PartnerProduct-Names
>
> what i need:
> a trigger or combination of trigger and SP which
> operate like the following:
> if on Partner2PartnerProduct is a Insert or Update made, then Delete
> the row in TempPartner which has the Id of the Partner, and then
> create a new row which contains all n:m joined PartnerProduct-Names in
> it.
>
> i hope the intention and problem are clear.
>
> thanx for answers,
> hans

The usual answer is that you should handle this in a client tool, rather
than in the database itself. There are ways to do it, but the only really
reliable way is a cursor (and if you need the comma-separated list in an
order, it's probably the only way), and cursors are slow:

http://www.aspfaq.com/show.asp?id=2279

Simon

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode