Newbie trigger question

I will apologize in advanced for spamming the group with newbie
questions. I do not have a reference in front of me and several google
attempts have come up on sucessful for the kind of information that I
am looking for. That being said here is my question/problem:

In a MSSQL database I want to create a trigger. I have 'product'
table where products can be linked together via a 'link' table to other
products. In this particular situation there can be a kind of master
product that other products are then cloned(i.e. they have the same
record values other than primary key) from (yes lot of data
redundancy!). If a clone is made from the master there is a  link
between the two id's of the 'product' table made in the 'link' table.
Now the trigger must coordinate the the master products prices with
the linked  products.

Here is a query that does what I want I just need it in trigger form.

update product
set price = (select price from product where prod_id = xxxyz)
where prod_id in (select related_id
from link
where id = xxxyz)

where xxxyz is the prod_id of the master product whose price is being
updated.

(I know this is not the ideal database set up but it is what I have to
work with )

- thanks!
Jake

-- Does the following Query do what your query is supposed to
-- If so the trigger should be about what you want.

UPDATE dest SET dest.price = src.price
-- SELECT dest.price, src.price, dest.prod_id, src.prod_id
FROM product src
JOIN link l ON src.prod_id = l.id
JOIN product dest ON l.related_id = dest.prod_id
WHERE dest.prod_id <> src.prod_id AND src.prod_id = xxxyz

CREATE TRIGGER product__update ON dbo.product
FOR UPDATE AS
BEGIN

UPDATE dest SET dest.price = src.price
FROM inserted src
JOIN link l ON src.prod_id = l.id
JOIN product dest ON l.related_id = dest.prod_id
WHERE dest.prod_id <> src.prod_id
-- AND dest.price <> src.price -- Add this if Price is a NOT NULL
Column
END

Tim S

Since I guess you are going to do multiple columns you should not use
"AND dest.price <> src.price"

Since it would only update if price is different.

Tim S

Please do not multi-post!

I answered in microsoft.public.sqlserver.programming

--
David Portas
SQL Server MVP
--


<alacrite@gmail.com> wrote in message
news:1127934231.076210.62130@g47g2000cwa.googlegroups.com...
>  I will apologize in advanced for spamming the group with newbie
> questions. I do not have a reference in front of me and several google
> attempts have come up on sucessful for the kind of information that I
> am looking for. That being said here is my question/problem:
>
>  In a MSSQL database I want to create a trigger. I have 'product'
> table where products can be linked together via a 'link' table to other
> products. In this particular situation there can be a kind of master
> product that other products are then cloned(i.e. they have the same
> record values other than primary key) from (yes lot of data
> redundancy!). If a clone is made from the master there is a  link
> between the two id's of the 'product' table made in the 'link' table.
>  Now the trigger must coordinate the the master products prices with
> the linked  products.
>
> Here is a query that does what I want I just need it in trigger form.
>
> update product
> set price = (select price from product where prod_id = xxxyz)
> where prod_id in (select related_id
> from link
> where id = xxxyz)
>
> where xxxyz is the prod_id of the master product whose price is being
> updated.
>
> (I know this is not the ideal database set up but it is what I have to
> work with )
>
> - thanks!
> Jake
>