Multi-Row update trigger

Hi,

I need to update LastReceivedQty and LastReceivedDate fields in the Product table each time a DeliveryNoteDetail entry is created for a PurchaseOrderDetail line.

DeliveryNote -> DeliveryNoteDetail -> PurchaseOrderDetail -> Product

DeliveryNote has the ReceivedDate
DeliveryNoteDetail has the ReceivedQty

I made the following trigger for handling single row updates, which works fine.

UPDATE Purchasing.Product
SET LastReceivedQty = i.ReceivedQty, LastReceivedDate = dn.ReceivedDate
FROM Purchasing.DeliveryNote dn INNER JOIN
Purchasing.DeliveryNoteDetail dnd ON dn.DeliveryNoteID = dnd.DeliveryNoteID INNER JOIN
inserted i ON dnd.DeliveryNoteDetailID = i.DeliveryNoteDetailID INNER JOIN
Purchasing.PurchaseOrderDetail pod ON dnd.PurchaseOrderDetailID = pod.PurchaseOrderDetailID INNER JOIN
Purchasing.Product p ON pod.VendorVendorProductID = p.VendorVendorProductID

Now I don't know how to handle multi-row situations when the same product is updated.
Since I cannot rely on the order that the updates are performed I need to somehow select the MAX(ReceivedDate).Subqueries, perhaps...
UPDATE Purchasing.Product
SET LastReceivedQty = subquery.ReceivedQty,
LastReceivedDate = subquery.ReceivedDate
from Purchasing.DeliveryNote dn
inner join --Subquery
(SELECT dnd.DeliveryNoteID,
sum(i.ReceivedQty) ReceivedQty,
max(dn.ReceivedDate) RecievedDate
FROM Purchasing.DeliveryNoteDetail dnd
INNER JOIN inserted i ON dnd.DeliveryNoteDetailID = i.DeliveryNoteDetailID
INNER JOIN Purchasing.PurchaseOrderDetail pod ON dnd.PurchaseOrderDetailID = pod.PurchaseOrderDetailID
INNER JOIN Purchasing.Product p ON pod.VendorVendorProductID = p.VendorVendorProductID
group by dnd.DeliveryNoteID) Subquery
on dn.DeliveryNoteID = Subquery.DeliveryNoteIDsql

multiple triggers

How can I set it so one trigger runs before another?
I have two triggers on a table and I need for one to run before the other.
Also is there a good book I can get that will explain writing triggers?Am Mon, 5 Jun 2006 09:15:02 -0700 schrieb Cynthia:

> How can I set it so one trigger runs before another?
> I have two triggers on a table and I need for one to run before the other.
> Also is there a good book I can get that will explain writing triggers?
You can only define which should be the first and which the last, but you
have only two triggers, so this is no problem. Here the description:
http://msdn2.microsoft.com/en-us/library/ms186762.aspx
bye, Helmut|||See if this helps:
Specifying First and Last Triggers
http://msdn2.microsoft.com/en-us/library/ms189568.aspx
AMB
"Cynthia" wrote:

> How can I set it so one trigger runs before another?
> I have two triggers on a table and I need for one to run before the other.
> Also is there a good book I can get that will explain writing triggers?

Multiple triggers

When a trigger on one table updates another table, does it activate
the update trigger on the updated table?

For ex:

1. Table A has a trigger for update
2. Table B has a trigger for update that updates table A

Will the trigger no table A run when table B trigger updates table A?

ThanksHi

If you update table B the trigger will update table A and the trigger on
table A will be activated. You will have to be careful that the actions you
perform within the do what you expect. There are 32 levels of recursion.

Check out Using Nested Triggers in Books Online.

John
"Yariv" <yariva@.mll.co.il> wrote in message
news:b4e534ce.0310080112.14d7aa18@.posting.google.c om...
> When a trigger on one table updates another table, does it activate
> the update trigger on the updated table?
> For ex:
> 1. Table A has a trigger for update
> 2. Table B has a trigger for update that updates table A
> Will the trigger no table A run when table B trigger updates table A?
> Thanks|||John Bell (jbellnewsposts@.hotmail.com) writes:
> If you update table B the trigger will update table A and the trigger on
> table A will be activated. You will have to be careful that the actions
> you perform within the do what you expect. There are 32 levels of
> recursion.

I'll add here, that is depends on the configuration option "nested
triggers". By defualt it is on. However, it is a little too easy to
turn it off in the Enterprise Manager. (Been there, done that.)

Note that if the trigger updates the table the trigger belongs to,
the default is that the trigger is *not* re-fired. This depends on
a database setting, RECURSIVE_TRIGGERS.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

multiple statements on insert trigger (mssql2000)

Can I have more than 1 statement fire on an insert trigger as I have
illustrated below, or do I use 2 insert triggers?
.....or is there a better solution?
Thanks Soc.
++++++++++++++++++++++++++++++++++++++++
+++++
CREATE TRIGGER [TRIG_trig1] ON [dbo].[table1]
FOR INSERT
AS
update table1 set column1=column2 where column3='GREEN' and column4 is null
update table1 set column5='peter' where column3='GREEN' where
column5<>'john'
++++++++++++++++++++++++++++++++++++++++
+++++soc
I'm not sure I inderstand you.
Triggers are fired per statement not per Insert. Why do you perform two
updating on the same table within a trigger, can explain what are you trying
to do?
"soc" <zxc0@.yahoo.com> wrote in message
news:%23al7PS2OFHA.1040@.TK2MSFTNGP12.phx.gbl...
> Can I have more than 1 statement fire on an insert trigger as I have
> illustrated below, or do I use 2 insert triggers?
> .....or is there a better solution?
> Thanks Soc.
> ++++++++++++++++++++++++++++++++++++++++
+++++
> CREATE TRIGGER [TRIG_trig1] ON [dbo].[table1]
> FOR INSERT
> AS
> update table1 set column1=column2 where column3='GREEN' and column4 is
null
> update table1 set column5='peter' where column3='GREEN' where
> column5<>'john'
> ++++++++++++++++++++++++++++++++++++++++
+++++
>
>|||Can an insert trigger do 2 updates along the lines of the trigger below?
"soc" <zxc0@.yahoo.com> wrote in message
news:%23al7PS2OFHA.1040@.TK2MSFTNGP12.phx.gbl...
> Can I have more than 1 statement fire on an insert trigger as I have
> illustrated below, or do I use 2 insert triggers?
> .....or is there a better solution?
> Thanks Soc.
> ++++++++++++++++++++++++++++++++++++++++
+++++
> CREATE TRIGGER [TRIG_trig1] ON [dbo].[table1]
> FOR INSERT
> AS
> update table1 set column1=column2 where column3='GREEN' and column4 is
> null
> update table1 set column5='peter' where column3='GREEN' where
> column5<>'john'
> ++++++++++++++++++++++++++++++++++++++++
+++++
>
>|||On Thu, 7 Apr 2005 12:25:52 +0100, soc wrote:

>Can I have more than 1 statement fire on an insert trigger as I have
>illustrated below, or do I use 2 insert triggers?
>.....or is there a better solution?
>Thanks Soc.
> ++++++++++++++++++++++++++++++++++++++++
+++++
>CREATE TRIGGER [TRIG_trig1] ON [dbo].[table1]
>FOR INSERT
>AS
>update table1 set column1=column2 where column3='GREEN' and column4 is null
>update table1 set column5='peter' where column3='GREEN' where
>column5<>'john'
> ++++++++++++++++++++++++++++++++++++++++
+++++
>
Hi soc,
You can use as many statements as you wish in the code of a trigger. But
you can't use two WHERE clauses in one UPDATE statement (as you do in
your second update).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)