| Author |
Newbie trigger question
|
|
| alacrite@gmail.com 2005-09-28, 8:24 pm |
| 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
| |
| David Portas 2005-09-28, 8:24 pm |
| 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
>
|
|
|
|