SQL Triggers And Navision 3.7

Friday13

I have created this trigger which will update my table (non-nav table) on insert.

However, it causing error on posting journal:

Item Ledger Entry already exists. Identification fields and values: Entry No.= '<Entry no>'

The trigger is basically reading the INSERTED table and update my non-nav table's quatity field or insert a record in the non-nav table.

Any help is greatly appreciated. Thanks in advance!

Eugene

it looks like you are trying to insert the same Item Ledger Entry record more than once. What trigger(s) are you having your code in ? and what the code is?

kriki

[Topic moved from Navision forum to SQL General forum]

Friday13

Eugene wrote:

it looks like you are trying to insert the same Item Ledger Entry record more than once. What trigger(s) are you having your code in ? and what the code is?

Basically the code reads from inserted.

It first check in the non-nav table for any existing item no_, model code, etc. If exist, it will update the non-nav table. Quantity field is updated.

If the item detail cannot be found, the trigger will insert a record into the Non-nav table.

Script as follow:

Alter TRIGGER "ItemLedgerInsert" ON dbo."CompanyName$Item Ledger Entry"
FOR INSERT
AS
If Exists(Select * from ItemInventory Where "ItemNo" = (Select "Common Item No_" FROM INSERTED)
And "ItemID" = (Select "Item No_" FROM INSERTED)
And "LocationCode" = (Select "Location Code" FROM INSERTED)
And "Item Manufacturer Code" = (Select "Item Manufacturer Code" FROM INSERTED)
And "Item Model No_" = (Select "Item Model No_" FROM INSERTED))
Begin
--Update
Update ItemInventory
set ItemInventory."Quantity" = ItemInventory."Quantity" + Cast(INSERTED."Quantity" As int)
From INSERTED
where "ItemNo" = "Common Item No_"
And "LocationCode" = "Location Code"

Update ItemInventory
set ItemInventory."Description" =
(Select "Description" + ' ' + "Description 2" From "CompanyName$Item"
Where "No_" = INSERTED."Item No_")
From INSERTED
where "ItemID" = "Item No_"
End
Else
Begin
If Exists(Select * from "CompanyName$Location" Where "LocationCode" = (Select "Location Code" FROM INSERTED)
And "Location Type" = 1)
Begin
--Insert
Insert Into "ItemInventory"
select ItemLedgerEntry."Item No_" ItemID, ItemLedgerEntry."Common Item No_" ItemNo,
Item."Description" + ' ' + Item."Description 2" As "Description",
ItemLedgerEntry."Location Code" LocationCode,
ItemLedgerEntry."Item Manufacturer Code",
ItemLedgerEntry."Item Model No_", 0 As ReservedQty,
sum(ItemLedgerEntry.Quantity),
Case When ItemLedgerEntry."Item Category Code" LIKE '%FURN'
THEN 'FURNITURE'
Else 'ELECTRICAL' END As ItemType
from INSERTED ItemLedgerEntry,
"CompanyName$Item" Item,
"CompanyName$Location" Location
Where
Location.Code = ItemLedgerEntry."Location Code"
And Location."Location Type" = 1
And ItemLedgerEntry."3rd Party Code" IN('N', 'T')
And Item."No_" = ItemLedgerEntry."Item No_"
group by ItemLedgerEntry."Location Code", ItemLedgerEntry."Item No_",
ItemLedgerEntry."Common Item No_", Item."Description" + ' ' + Item."Description 2",
ItemLedgerEntry."Item Manufacturer Code",
ItemLedgerEntry."Item Model No_", ItemLedgerEntry."Item Category Code"
End
End

DenSter

That's probably because you are updating the record after it is inserted from SQL Server, as part of the posting process in NAV. Because it's within the transaction the entry is not considered to be committed yet, it recognizes that the record in the database is different from the one it tries to create itself, and you get that error.

What you should really do is make the thing that you are doing in the SQL Server trigger part of the posting routine in NAV, and use ADO or something like that. You could even create a linked NAV table based on the non-NAV table and treat it as a regular NAV table in C/AL code.

VoltaixIT

I have the same error using the Sales Shipment Lines table. I am using NAV2009. We are using another program to manage returnable containers, I need to send some information to it when Warehouse Shipments are posted.

I am able to update the external table with variables created from the inserted record with no problems, I just get the error when I try to use the inserted record or the variables as part of a select statement to get other data.

I know its been years... but has anyone found a work around for this? I really don't want to modify NAV code for this simple task.

davmac1

NAV transactions are committed at the end of the process so they can be rolled back if unsuccessful. It is not worthwhile trying to overcome this process by adding additional commits within NAV, because if there is an error you are left with partially updated tables.
As Denster wrote - do it in NAV. It is the best and safest way since what you are wanting to do is really a part of the overall transaction.

rmv_RU

Friday13 wrote:

I have created this trigger which will update my table (non-nav table) on insert.
However, it causing error on posting journal:
Item Ledger Entry already exists. Identification fields and values: Entry No.= '<Entry no>'
The trigger is basically reading the INSERTED table and update my non-nav table's quatity field or insert a record in the non-nav table.
Any help is greatly appreciated. Thanks in advance!

Just add "set nocount on" as a first statetment of a trigger.

Because it's within the transaction the entry is not considered to be committed yet, it recognizes that the record in the database is different from the one it tries to create itself, and you get that error.

In my opinion it's not true. Sift trigger works fine .

use ADO or something like that

Never do it! Because there are no way to use same connection for Navision and Ado objects.