Autoincrement key log primary key doesn't work

PureHeart

Hi everyone,

I have a challenging problem to solve...
I'm trying to log the value of the primary key of any table when a new record is inserted ... it all works well but not with tables that have the autoincrement pk... the reason is that the database insert trigger ( codeunit 1) is fired before the insertion so Nav does not know the value of the pk that will be calculated by sql... this means I'm always logging a pk of zero which is not correct... so any ideas on how to solve this and be able to log the correct pk of any table in Nav with an autoincrement? I cannot operate on single table events it must be generic for any table in NAV..

Hope someone has got the solution I don't have!

Cheers!!!

PureHeart

No one? No idea at all? Or you too don't have a solution?

Slawek_Guzek

I'm afraid integration events are the only way to go. You can create a codeunit with a subscriber function subscribed to OnAfterInsert event on your table. Then from this function you need to call your logging stuff in cu1.

Not ideal as you need to code one subscriber function for each table with autoincrement int in PK.. But on the other hand only tables having autoincrement field on PK needs to be handled that way - all others can be handled by the trigger in the CU1.

Another idea is that you can add missing information (missing PK value) to your log table later on, on the next trigger call. On each call simply find all zeros, and update them with latest values from relevant tables. If you also code OnGlobalDelete and Modify you will catch all the cases.

PureHeart

Thanks but the solution should be generic and listening to events to specific tables makes it not generic...said that I don't think there is any other way ... I don't understand why Microsoft hasn't provided two triggers in cu1 before insert and after insert like in any other Nav table...makes no sense...

I can't add the missing information because on update how do I know to what record does belong a logged entry without pk?

Slawek_Guzek

If you insert an entry with non-compound auto-increment PK the 0 entry for the table will be the previous insert, so the last existing record (unless you have deleted the last entry or forced identity value in it - but OnDelete trigger will handle deletion, and with forced identity insert you will see new vaue in OnInsert trigger as it will be set inthe code).

For compound PKs where auto-increment is one of PK field - that would be quite weird design, but if you are not in full control of you app might happen. Again - you find the latest record with 0 in auto-increment key (it should be only one), non auto-increment values will be correctly saved on previous call, so you will be able to find last existing record in the table having non auto-increment field values

Slawek_Guzek

PureHeart wrote: »

I can't add the missing information because on update how do I know to what record does belong a logged entry without pk?

You know the table ID so you know the PK on it..

Imagine the scenario - a new table with single column called My PK and type int identity.

A)
The very first insert - in OnGlobalInsert My PK field is 0, and the table is empty. What gets logged is the table id and value of PK which shows My PK columns and value of 0. The actual table receives a record with 1 in My PK column after your OnGlobalInsert finished

B )
The second insert - in OnGlobalInsert My PK field is still 0, but this time there is a) a single Insert entry in your log table with your table ID, and value of 0 in My PK field, and there is also a single record in the table - with My PK value of 1. Before running the code logging the current insert you search for the last log entry for the table, and last entry in the table itself - and this is the missing pair.

After OnGlobalInsert is finished you will have: a) old entry updated with the last value of PK found in the actual table - which is exactly the PK field value inserted 'previous' time, b) new entry in the log table with the same table ID and value of 0 in My PK field

C) another insert - repeat point B ).

In any given time you will have in your log table exactly one entry with 0 in PK column for each table ID having autoincrement field in PK.

PureHeart

There are too many reads in your solution. When my log table is read, if I found that the PK is zero and it is an auto increment PK I would then have to read the real table and get the last record...

I opted for a less generic solution for tables with the auto increment pk, I'm listening to the OnAfterInsertEvent of the tables to track the correct pk.