SQL Triggers And Navision 3.7

Friday13Friday13 Member Posts: 3
edited 2013-05-16 in SQL General
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!

Comments

  • EugeneEugene Member Posts: 309
    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?
  • krikikriki Member, Moderator Posts: 9,094
    [Topic moved from Navision forum to SQL General forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • Friday13Friday13 Member Posts: 3
    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
  • DenSterDenSter Member Posts: 8,304
    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.
  • VoltaixITVoltaixIT Member Posts: 6
    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.
  • davmac1davmac1 Member Posts: 1,283
    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_RUrmv_RU Member Posts: 119
    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.
    Looking for part-time work.
    Nav, T-SQL.
Sign In or Register to comment.