I know this is a little OT, but i have been looking at this for hours and cannot see
what the actual error is.
Can someone look at this MSSQL trigger and tell me where the error is? i know it is
around the if statements i commented around, but cannot figure out what the syntax
error is!
Thanks in advanced!
andres
CREATE TRIGGER dbo.salesorderdetail_aiu
ON dbo.OrderDetails
FOR INSERT,UPDATE AS
BEGIN
DECLARE @WEBsalesorderdetailid int,
@WEBsalesorderid int,
@WEBCustomerId int,
@WEBproductid int,
@qtyordered float,
@saleprice money,
@unitprice money,
@discount money,
@extendedprice money,
@SKUNumber varchar(15),
@proddesc varchar(60),
@sono char(10),
@custno char(6),
@COUNTER NUMERIC,
@thissono char(10),
@itemnum varchar,
@nextlineno int,
@ordamt money,
@shipping money,
@shipmethodid int,
@goldorderflag char(1),
@goldorderfrequency integer,
@pterms varchar(20)
SELECT @WEBsalesorderdetailid = SalesOrderDetailID,
@WEBsalesorderid = SalesOrderId,
@WEBproductid = ProductId,
@qtyordered=QuantityOrdered,
@unitprice=UnitPrice,
@saleprice=SalePrice,
@discount=Discount,
@goldorderflag = inserted.goldorderflag,
@goldorderfrequency = inserted.goldorderfrequency
FROM inserted
SELECT @SKUNumber=substring(rtrim(SKUNumber),1,15),@proddesc =
substring(rtrim(ProductName),1,60)
FROM Products
WHERE ProductId = @WEBproductid
SELECT @sono=sono,@custno = custno
FROM somast01
WHERE WEBSalesOrderId = @WEBsalesorderid
SELECT @pterms = pterms
FROM arcust01
WHERE WEBCustomerId = @custno
BEGIN
BEGIN TRANSACTION
--sql server is telling me there is a syntax error somewhere between here...
If isnumeric(@discount)
begin
if @discount > 0
SELECT @extendedprice = (@saleprice *
@qtyordered) - ((@saleprice * @qtyordered) * (@discount/100))
end
else
SELECT @extendedprice = @saleprice * @qtyordered
-- and here!!!!
SELECT LINENUM FROM sotran01 where sono = @sono and LINENUM <
99
IF @@rowcount <= 0
SELECT @nextlineno = 1
ELSE
SELECT @nextlineno = max(LINENUM)+1 FROM sotran01
where sono = @sono and LINENUM < 99
print @nextlineno
INSERT INTO sotran01(
WEBSALESORDERDETAILID,
SONO,
CUSTNO,
ITEM,
DESCRIP,
DISC,
COST,
PRICE,
QTYORD,
EXTPRICE,
LINENUM,
GOLDORDERFLAG,
GOLDORDERFREQUENCY,
PTERMS)
VALUES
(convert(char,@WEBsalesorderdetailid),@sono,@custno,@SKUNumber,@proddesc,@discount,@unitprice,@saleprice,@qtyordered,@extendedprice,@nextlineno,@goldorderflag,@goldorderfrequency,@pterms)
COMMIT TRANSACTION
SELECT @ordamt=SUM((PRICE-ISNULL(DISC,0))*QTYORD)
FROM sotran01
WHERE SONO = @sono
SELECT @shipmethodid = ShippingMethodId
FROM Orders
WHERE SalesOrderId = @WEBsalesorderid
SELECT @shipping = ShippingMethodShippingCharge
FROM ShippingMethods
WHERE ShippingMethodID = @shipmethodid
SELECT @ordamt = @ordamt+@shipping
BEGIN
UPDATE somast01
SET ordamt = @ordamt
WHERE WEBSalesOrderId = @WEBsalesorderid
END
END
END
---
VB.NET & SQL Server 2000: Building an Effective Data Layer
This book will teach the experienced VB developer, with a working
knowledge of Visual Basic .NET, how to integrate SQL Server 2000
with VB .NET applications using ADO.NET. "Building an effective
data layer" illustrates how to take advantage of the new features
available in the .NET Framework when working with data-centric
applications.
For more information: http://www.wrox.com/ACON11.asp?ISBN=1861007051
---
Change your mail options at http://p2p.wrox.com/manager.asp or
to unsubscribe send a blank email to [EMAIL PROTECTED]
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
