Need trigger to fire on Insert and Update - works on Update only

[ajaeger]

Here's the trigger that I have. I need it to work when a new record is inserted in the Orders table. Currently it only works when an existing Orders record is updated.

Create trigger [dbo].[UpdateMeetFunc-Insert]
On [dbo].[Orders]
After Insert, Update As
Begin
;with cte as (
select Orders.Order_Number, dbo.FuncGetOrdersDesc(Orders.Order_Number) as Updated_Value from Orders)
update Order_Meet
Set ADDITIONAL_BADGES = cte.Updated_Value from Order_Meet inner join cte on Order_Meet.Order_Number = cte.Order_Number
End

Thanks.

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[gmmastros]

That's happening because you are using the Orders table, but the data you are inserting does not exist in that table yet.

Instead, I recommend you use the 'hidden' Inserted table.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Change Orders in your first query to Inserted as George suggested.

 

[ajaeger]

I think I have this trigger set-up correctly, but it is not updating Order_Meet.ADDITIONAL_BADGES on INSERT, only UPDATE. I think the issue is that this trigger references a function, FuncGetOrdersDesc, that pulls from records in the tables that don't exist until the record is saved - the Order_Lines, Orders, and Order_Meet tables. I'm just not sure how to get around it.

Create trigger [dbo].[UpdateMeetFunc]
On [dbo].[Orders]
After Insert, Update As
Begin;with cte as
(select Inserted.Order_Number, dbo.FuncGetOrdersDesc(Inserted.Order_Number) as Updated_Value from Inserted)
update Order_Meet
Set ADDITIONAL_BADGES = cte.Updated_Value
from Order_Meet inner join cte on Order_Meet.Order_Number = cte.Order_Number
End

The Function is:

create FUNCTION [dbo].[FuncGetOrdersDesc](@OrderNumber int)
RETURNS varchar(max)
AS
BEGIN
DECLARE @RetVal varchar(max)
SELECT @RetVal = ISNULL((@RetVal + CHAR(13)+CHAR(10)),'') + Order_Lines.DESCRIPTION + ' - Qty. '
+ convert(varchar(10), Order_Lines.QUANTITY_ORDERED) + ' - $' + convert(varchar(10),Order_Lines.EXTENDED_AMOUNT)
FROM Order_Lines, Orders, Order_Meet, Product_Function
WHERE Order_Lines.ORDER_NUMBER = @OrderNumber and
Orders.ORDER_NUMBER=Order_Lines.ORDER_NUMBER and Orders.ORDER_NUMBER=Order_Meet.ORDER_NUMBER and
Order_Lines.PRODUCT_CODE=Product_Function.PRODUCT_CODE and
Orders.STATUS not in ('C','CT') and Order_Lines.QUANTITY_ORDERED>0 and LINE_NUMBER not like '%.%'
ORDER by Product_Function.SEQ

select @RetVal = substring(@RetVal, 1, (len(@RetVal)-0))

RETURN @RetVal
END

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[markros]

Make your trigger for update only.

In case of insert you may need to update this field in the SP that does the insert for all these tables as the last step in this SP. I don't see any other way here - if you're using transaction for all the operations, the final update should be OK after all inserts are finished.