May 14, 2009 at 9:44 am
Below is my sample script to update the
following dblSubtotalDetail ,dblsubtotaldetailRate ,dblSalesPrice ,dblSalesPriceRate from Table A when I inserted a data in Table B using the trigger but it seems that it is not working, When i execute the scripts no error founds can you share some light on me?
I will really appreciate it:-D
I'm looking forward for your help
Thanks
DECLARE @OrderItemTypeID int
,@sku nvarchar(100)
,@ACorderID int
SELECT top 1 @ACorderID =orderID FROM inserted
SELECT @SKU=sku FROM ac_orderItems where orderitemtypeID = 0 and orderID =@AcOrderId
SELECT Top 1 @OrderItemTypeID=orderitemtypeid FROM inserted
if @OrderItemTypeID=4
GOTO Discount
else if @OrderItemTypeId = 3
Goto Tax
else
GOTO OncePerrow
--Discount
Discount:
DECLARE @Price decimal(12,4)
,@DiscountPrice decimal(12,4)
,@ItemsName nvarchar (100)
,@ItemsDiscount nvarchar(100)
,@DiscountPercent decimal(12,4)
,@Quantity decimal(12,4)
SELECT top 1
@ItemsName=a.name
,@Quantity = a.Quantity
,@Price=a.price
,@ItemsDiscount=b.name
,@DiscountPrice=b.price
FROM ac_OrderItems a
INNER JOIN ac_OrderItems b
ON b.name=a.sku
WHERE a.OrderID = @acOrderID and b.orderID = @acOrderID and Isnull(a.intvccntID,'')<>'' and Isnull(b.intvccntID,'')=''
DECLARE @Result decimal (12,4)
SET @Result = (@Price - ((@DiscountPrice * (-1))/@Quantity))
Update [VCsamplecompany].dbo.tblOESalesDetail
SET dblSubtotalDetail=@Result
,dblsubtotaldetailRate = @Result
,dblSalesPrice=@Result
,dblSalesPriceRate = @Result
FROm [VCsamplecompany].dbo.tblOESalesDetail
WHERE intACOrderItemID in
(SELECT orderItemID from ac_orderItems
where sku = @sku and orderID = @acOrderID and OrderItemTypeID=0)
May 14, 2009 at 1:37 pm
Hi
It's very hard to help without some sample data or more information. As you say the trigger (part you posted) doesn't generate a syntax error. So it depends on your data.
Did you check:
1.)
Is the value of @OrderItemTypeId "4"?
2.)
Is your JOIN really correct? Are there two rows in your ac_OrderItems which can be joined over "name" and "sku"? Do both of these rows have the same "OrderId" (value = @acOrderOD). Is "intvccntID" set in both of these rows?
Some design issues:
* Your trigger will run into problems with bulk operations because it handles only the first item.
* Try to avoid GOTO :sick:
Greets
Flo
May 14, 2009 at 4:49 pm
Thanks,
Yup i do check the value of the variable and it seems that they are correct, i suspect that there is some thing wrong with my join but i can't figure it out 🙁
May 15, 2009 at 2:49 am
So take a valid value for your @acOrderID.
Start with your first JOIN (the SELECT which fills the other variables) and copy into a separate query window. So you can figure out if it works.
After that same with the second JOIN (your UPDATE statement). Copy to another query window. Replace the UPDATE part by a SELECT clause and make it work.
Greets
Flo
May 15, 2009 at 10:57 am
Hi flo,
Thanks for the time it's an odd thing but believe me when i do that like what you have said the Update statement works. But in the trigger it does not 🙁 I give you a sample data)
Table A
Name
Item A
UPS NExt Day
ITEM A
SKU
ItemA
Null
23
Price
125.00
0.00
-5.00
intVCCntid
151
Null
Null
Quantity
2
1
1
Computation of Discount
get the Original Price of an item with OrderItemTypeID = 0 and orderID = 18 (125.00)
get the Price of an item in where Name = SKU and the intVCCntID is not null and the orderItemTypeID = 0 and OrderID = 18(-5.00)
get the Quantity of an item where orderItemTypeID = 0 and orderID = 18(2 qty)
Computed Discount= 125-(-5.00 *(-1)/2)
Table 2
strProductID cntID dblsalesPrice
-------------------------------------------------- -----------
ITEMA 151 125.000000
Then Update the Table2 set dblSalesPrice = Computed Discount where cntID(151) = Tablea intVCcntID (151)
May 15, 2009 at 11:06 am
Hi hunter
I really want to help. Could you please provide DDL (CREATE statements) for your sample data, some INSERT-statements for the sample data and the SELECT you are using? Problem is your sample data do not completely match to your initial trigger. I want to avoid a wrong solution 😉
You can find a link in my signature which should be really helpful for providing some sample data.
Greets
Flo
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply