May 20, 2009 at 1:24 pm
Hi Guys,
Can you take a look at my trigger.
ERROR Encounter: The INSERT statement conflicted with the FOREIGN KEY constraint "ac_Orders_ac_Shipments_FK1". The conflict occurred in database "ablecommerce", table "dbo.ac_Orders", column 'OrderId'. The statement has been terminated.
Please i need your help badly :crying:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [trg_SyncAC_Orders]
ON [dbo].[ac_Orders]
FOR INSERT, UPDATE
AS
begin
UPDATE [Database].dbo.tblOESales
set intACOrderID = I.OrderID
FROM inserted I
WHERE cntID = I.intVCCntID
declare @intIsValid int
/**************************************************************************************************************************************/
/*** For ICE Architecture (Do not alter) ***/
EXEC @intIsValid = ICE_SyncAC_Orders_InsertUpdate_Initialize
IF @intIsValid <> 0GOTO Post_Rollback
declare @user-id int
declare @OrderID int
declare @OrderDate datetime
declare @BillToFirstName nvarchar(30)
declare @BillToLastName nvarchar(50)
declare @BillToCompany nvarchar(50)
declare @BillToAddress nvarchar(255)
declare @BillToAddress2 nvarchar(255)
declare @BillToCity nvarchar(50)
declare @BillToState nvarchar(50)
declare @BillToZip nvarchar(15)
declare @BillToCountry nvarchar(25)
declare @BillToPhoneNumber nvarchar(50)
declare @BillToFax nvarchar(50)
declare @Email nvarchar(255)
declare @SubOrderTotal decimal(12,4)
declare @OrderTotal decimal(12,4)
declare @Payment decimal(12,4)
declare @OrderStatus int
declare @strOrderStatus nvarchar(40)
Declare @CustomerID nvarchar(40)
Declare @CurrencyID nvarchar(255)
Declare @ShipMethodName nvarchar(3)
Declare @strNewNumber nvarchar(255)
declare @currencymult decimal(12,4)
Declare @strwarehouseid nvarchar(255)
Declare @shiptocompany nvarchar(50)
Declare @shiptoaddress nvarchar(100)
Declare @shiptocity nvarchar(30)
Declare @shipmethodid nvarchar(25)
Declare @shiptostate nvarchar(5)
Declare @shiptozip nvarchar(12)
Declare @shiptophone nvarchar(20)
Declare @shiptofirstname nvarchar(25)
Declare @shiptolastname nvarchar(25)
Declare @shiptocountry nvarchar(25)
Declare @shiptofax nvarchar(20)
Declare @shipvia nvarchar(25)
Declare @SalesAcct nvarchar(25)
Declare @LinkedOrders nvarchar(20)
DECLARE c CURSOR
FOR SELECT UserID, OrderID, OrderDate, BillToFirstName, BillToLastName, isnull(BillToCompany, ''),
BillToAddress1, BillToAddress2, BillToCity, BillToProvince, BillToPostalCode,
countries.Name, BillToPhone, BillToFax, BillToEmail, ProductSubTotal,
TotalCharges, TotalPayments, OrderStatusID
FROM inserted
INNER JOIN ac_Countries AS countries
ON inserted.BillToCountryCode = countries.CountryCode
OPEN c
FETCH NEXT FROM c INTO @userid, @OrderID, @OrderDate, @BillToFirstName, @BillToLastName, @BillToCompany,
@BillToAddress, @BillToAddress2, @BillToCity, @BillToState, @BillToZip,
@BillToCountry, @BillToPhoneNumber, @BillToFax, @Email, @SubOrderTotal,
@OrderTotal, @Payment, @OrderStatus
WHILE (@@FETCH_STATUS=0) BEGIN
declare @result bit
EXEC [Database].[dbo].[usp_BridgeCheckOrder]
@intOrderID = @OrderID,
@result = @result OUTPUT
SELECT @result
if (@result = 0)
begin
EXEC [Database].[dbo].[usp_ReturnStartingNumber]
@intModule = 39,
@strNewNumber = @strNewNumber OUTPUT
SET @LinkedOrders = @strNewNumber
EXEC [Database].[dbo].[usp_ReturnStartingNumber]
@intModule = 3,
@strNewNumber = @strNewNumber OUTPUT
SET @CustomerID = isnull((SELECT top 1 strCustomerID FROM [Database].dbo.tblARCustomer WHERE intACUserID = @userid),'')
if (@CustomerID = '')
begin
SET @CustomerID = isnull((SELECT TOP 1 strCustomerID FROM [Database].dbo.tblARCustomer WHERE strWebName = 'anonymous@user.com'), '')
end
SET @CurrencyID = (SELECT TOP 1 ISOCode FROM AC_Currencies)
SET @currencymult = (SELECT TOP 1 dblActualRate FROM [Database].dbo.tblSMCurrency WHERE strCurrencyID = @CurrencyID)
SET @SalesAcct = (SELECT top 1 strAccountID FROM [Database].dbo.tblglaccountdefault WHERE strUserID = 'Admin' AND strDefaultName = 'strSalesAR')
set @strOrderStatus = CASE @OrderStatus
WHEN 1 THEN 'Pending'
WHEN 2 THEN 'In Process'
WHEN 3 THEN 'Complete'
WHEN 4 THEN 'Pending'
WHEN 5 THEN 'On Hold'
WHEN 6 THEN 'On Hold'
ELSE 'On Hold'
END
set @shipvia = isnull((SELECT top 1 ShipMethodName FROM ac_OrderShipments WHERE OrderID = @OrderID), 'Deliver')
set @strwarehouseid = (SELECT top 1 strWarehouseID FROM [Database].dbo.tblICWarehouse)
SELECT top 1
@shiptocompany = Company
, @shiptoaddress = substring(Address1 + ' ' + isnull(Address2, ''), 0, 100)
, @shiptocity = City
, @shiptostate = Province
, @shiptozip = PostalCode
, @shiptophone = Phone
, @shiptofirstname = FirstName
, @shiptolastname = LastName
, @shiptocountry = c.Name
, @shiptofax = Fax
FROM ac_Addresses AS a, ac_Users AS u, ac_Countries AS c WHERE a.AddressID = u.PrimaryAddressID AND u.userid = @userid AND c.CountryCode = a.CountryCode
INSERT INTO [Database].dbo.tblOESales
(
strOrderNumber,
intACOrderID,
dtmShipDate,
dtmCancelDate,
dtmDate,
dtmCreated,
dtmDateEntered,
strCustomerID,
strBillToFirstName,
strBillToLastName,--10
strBillToCompany,
strBillToAddress,
strBillToCity,
strBillToState,
strBillToZip,
strBillToCountry,
strBillToPhoneNumber,
strBillToFax,
strEmail,
dblSubOrderTotal, --20
dblOrderTotal,
dblPayment,
strOrderStatus,
strCompanyName,
strOriginalOrderNumber,
strTransactionType,
strShipVia,
strTerms,
strCurrencyID,
ysnPaid, --30
ysnShipped,
ysnTermsDiscount,
ysnPrintCost,
ysnCompleted,
ysnInvoice,
ysnPOCreated,
strFreightTaxID,
strTaxCodeFreight,
dblWeightTotal,
dblVolumeTotal, --40
ysnPrintAuto,
ysnRemote,
ysnAcceptBO,
ysnShipBlind,
ysnCreditCardAuth,
strAccountID,
strEndUserPO
,[dblAmountDue]
,[dblAmountDueRate]
,[dblCreditCardAmountRate]--50
,[dblLabor]
,[dblLaborRate]
,[dblPaymentRate]
,[dblSubOrderTotalRate]
,[dblSubTotalDetailRate]
,[strFOB]
,[strWarehouseID]
,[strShipToCompany]
,[strType]
,[strShipToAddress]--60
,[strShipToCity]
,[strShipToState]
,[strSalesRepGroupID]
,[strShipToZip]
,[strSalesperson]
,[strShipToPhoneNumber]
,[strRepOrderNumber]
,[strCustomerPO]
,[strShipID]
,[strShipFromID]--70
,[strShipToFirstName]
,[strShipFromName]
,[strShipToLastName]
,[strShipFromAddress]
,[strShipFromCity]
,[strShipFromState]
,[strShipFromCounty]
,[strShipFromCountry]
,[strShipFromZip]
,[strShipToID]
,[strShipToAttention]--80
,[strShipToSalutation]
,[strShipToCareOf]
,[strShipToCounty]
,[strShipToCountry] --84
,[strShipToFax]
,[strBillToID]
,[strBillToCareOf]
,[strBillToAttention]
,[strBillToSalutation]
,[strBillToCounty]
,[strNotes]
,[strVendorInvoices]
,[strPaymentInfo]
,[strMessage]
,[strDiscID]
,[strSalesTaxCode]
,[strLinkedOrders]
,[dblSalesTax]
,[dblSalesTax2]
,[dblFreight]
,[dblFreightRate]
,[dblFreightTax]
,[dblFreightTax2]
,[dblHourly]
,[dblOther]
,[dblHourlyRate]
,[dblSalesTaxAmount2]
,[dblOtherRate]
,[dblSalesTaxRate]
,[dblSalesTaxAmount]
,[dblDiscount]
,[dblOrderTotalRate]
,[dblMHRTotal]
,[dblSubForm]
,[dblSalesTaxRate2]
,[dblExchangeRate]
,[intTaxOther]
,[intTaxFreight]
,[ysnCreditCardProcessed]
,[ysnManualTax]
,[strPaymentMethod]
,[strCreditCardType]
,[strCreditCardCSC]
,[strCreditCardReference]
,[strCreditCardFirstName]
,[strCreditCardLastName]
,[strCreditCardAddress]
,[strCreditCardCity]
,[strCreditCardCountry]
,[strCreditCardState]
,[strCreditCardZip]
,[strCreditCardAVS]
,[strCreditCard]
,[strCreditCardName]
,[strCreditCardExpMonth]
,[strCreditCardExpYear]
,[dtmCreditCardProcessed]
,[dblCreditCardAmount]
,[strCCAuthNumber]
,[strTaxScheme]
,[strCommissionBatch]
,[strInvoice]
,[strUserUpdated]
,[strSalesTaxCode2]
,[strTimeStamp]
,[strPaymentReference]
,[strSource]
,[strUserCreated]
,[strReceivableAR]
,[dtmUserUpdated]
,[dblVATAmount]
,[dblVATAmountRate]
,[dblCommissionAmountTotal]
,[dblOtherTax]
,[dblOtherTax2]
,[dblMHRTax]
,[dblMHRTax2]
,[dblTaxMHR]
,[dblTaxFreight]
,[intPallets]
,[intBoxes]
,[intLength]
,[intWidth]
,[intHeight]
,[intPackages]
,[intConcurrencyID]
,[strTaxCodeMHR]
,[strTaxCodeOther]
)
VALUES (
@strNewNumber
, @OrderID
, @OrderDate
, dateadd(day, -1, dateadd(month, 1, @OrderDate))
, @OrderDate
, @OrderDate
, @OrderDate
, @CustomerID
, substring(@BillToFirstName, 0, 25)
, substring(@BillToLastName, 0, 25)--10
, substring(@BillToCompany, 0, 50)
, substring(@BillToAddress + ' ' + isnull(@BillToAddress2, ''), 0, 100)
, substring(@BillToCity, 0, 30)
, substring(@BillToState, 0, 25)
, @BillToZip
, substring(@BillToCountry, 0, 25)
, substring(@BillToPhoneNumber, 0, 20)
, substring(@BillToFax, 0, 20)
, substring(@Email, 0, 75)
, @SubOrderTotal--20
, @OrderTotal
, 0--@Payment
, @strOrderStatus
, substring(@BillToCompany, 0, 50)
, ''
, N'Order'
, @ShipVia
, N'NONE'
, N'USD'
, 0 --30
, 0
, 0
, 0
, 0
, 0
, 0
, 'NONE'
, 'NONE'
, 0
, 0--40
,0
,0
,0
,0
,0
,@SalesAcct
,N'NONE'
, @OrderTotal
, @OrderTotal * @currencymult
, 0--50
, 0
, 0
, 0
, @SubOrderTotal * @currencymult
, 0
, substring(@strWarehouseID, 0, 15)
, ''
, substring(@shiptocompany, 0, 50)
, ''
, substring(@shiptoaddress, 0, 100)--60
, substring(@shiptocity, 0, 30)
, substring(@shiptostate, 0, 5)
, ''
, @shiptozip
, ''
, substring(@shiptophone, 0, 20)
, ''
, ''
, ''
, ''--70
, substring(@shiptofirstname, 0, 25)
, ''
, substring(@shiptolastname, 0, 25)
, ''
, ''
, ''
, ''
, ''
, ''
, ''--80
, ''
, ''
, ''
, substring(@shiptocountry, 0, 25)--84
, substring(@shiptofax, 0, 20)
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, 'NONE'
, @LinkedOrders
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, @OrderTotal * @currencymult
, 0
, 0
, 0
, 1 / @currencymult
, 0
, 0
, 0
, 0
, ''
, ''
, ''
, ''
, substring(@BillToFirstName, 0, 25)
, substring(@BillToLastName, 0, 25)
, substring(@BillToAddress + ' ' + isnull(@BillToAddress2, ''), 0, 100)
, substring(@BillToCity, 0, 30)
, substring(@BillToCountry, 0, 25)
, substring(@BillToState, 0, 25)
, @BillToZip
, ''
, ''
, ''
, ''
, ''
, @orderdate
, @ordertotal
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, 'Internet'
, ''
, ''
, @OrderDate
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, ''
, ''
)
end
FETCH NEXT FROM c INTO @userid, @OrderID, @OrderDate, @BillToFirstName, @BillToLastName, @BillToCompany,
@BillToAddress, @BillToAddress2, @BillToCity, @BillToState, @BillToZip,
@BillToCountry, @BillToPhoneNumber, @BillToFax, @Email, @SubOrderTotal,
@OrderTotal, @Payment, @OrderStatus
END
print '--after--'
CLOSE c
DEALLOCATE c
---------update
SET @CurrencyID = (SELECT TOP 1 ISOCode FROM AC_Currencies)
SET @currencymult = (SELECT TOP 1 dblActualRate FROM [Database].dbo.tblSMCurrency WHERE strCurrencyID = @CurrencyID)
SET @CustomerID = isnull((SELECT top 1 strCustomerID FROM [Database].dbo.tblARCustomer WHERE intACUserID = @userid),'')
if (@CustomerID = '')
begin
SET @CustomerID = isnull((SELECT TOP 1 strCustomerID FROM [Database].dbo.tblARCustomer WHERE strWebName = 'anonymous@user.com'), '')
end
UPDATE [Database].dbo.tblOESales SET
dtmDate = inserted.OrderDate,
dtmCreated = inserted.OrderDate,
dtmDateEntered = inserted.OrderDate,
strCustomerID = @CustomerID,
strBillToFirstName = substring(inserted.BillToFirstName, 0, 25),
strBillToLastName = substring(inserted.BillToLastName, 0, 25),
strBillToCompany = substring(inserted.BillToCompany, 0, 50),
strBillToAddress = substring(inserted.BillToAddress1 + ' ' + isnull(inserted.BillToAddress2, ''), 0, 100),
strBillToCity = substring(inserted.BillToCity, 0, 30),
strBillToState = substring(inserted.BillToProvince, 0, 25),
strBillToZip = inserted.BillToPostalCode,
strBillToCountry = countries.Name,--substring(inserted.BillToCountryCode, 0, 25),
strBillToPhoneNumber = substring(inserted.BillToPhone, 0, 20),
strBillToFax = substring(inserted.BillToFax, 0, 20),
strEmail = substring(inserted.BillToEmail, 0, 75),
dblSubOrderTotal = inserted.ProductSubTotal,
dblOrderTotal = inserted.TotalCharges,
dblPayment = 0,--inserted.TotalPayments,
dblOrderTotalRate = inserted.TotalCharges * @currencymult,
dblSubOrderTotalRate = inserted.ProductSubTotal * @currencymult,
dblAmountDue = inserted.TotalCharges,
dblAmountDueRate = inserted.TotalCharges * @currencymult--,
FROM inserted
INNER JOIN deleted
ON deleted.OrderId = inserted.OrderId
INNER JOIN ac_Countries AS countries
ON inserted.BillToCountryCode = countries.CountryCode
WHERE
intACOrderID = deleted.OrderId
-- once per row
Declare @strOrderNumber nvarchar(255)
DECLARE c2 CURSOR
FOR SELECT I.OrderID
FROM inserted I
OPEN c2
FETCH NEXT FROM c2 INTO @OrderID
WHILE (@@FETCH_STATUS=0) BEGIN
SELECT TOP 1 @strOrderNumber = strOrderNumber FROM [Database].dbo.tblOESales WHERE intACOrderID = @OrderID
/**************************************************************************************************************************************/
/*** For ICE Architecture (Do not alter) ***/
EXEC @intIsValid = ICE_SyncAC_Orders_InsertUpdate_OncePerRow @OrderID, @strOrderNumber
FETCH NEXT FROM c2 INTO @OrderID
END
CLOSE c2
DEALLOCATE c2
IF @intIsValid <> 0GOTO Post_Rollback
/**************************************************************************************************************************************/
/*** For ICE Architecture (Do not alter) ***/
EXEC @intIsValid = ICE_SyncAC_Orders_InsertUpdate_Exiting
IF @intIsValid <> 0GOTO Post_Rollback
GOTO Post_Exit
Post_Rollback:
ROLLBACK TRANSACTION
Post_Exit:
SELECT top 1 * FROM inserted
end
May 20, 2009 at 3:11 pm
What's the structure of dbo.ac_Orders? What does ac_Orders_ac_Shipments_FK1 point to? It doesn't look like this particular error is in your trigger. It looks like it is in your orignal insert into dbo.ac_Orders since the FK violation is on that table.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 20, 2009 at 4:40 pm
Thanks,
Attached to this reply is a sample screen shots of my table structure, Hope it will help you a lot.
Thanks so much
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply