Catch insert, update and delete actions

skalp

Hi,

I have to create similar functionality like Change Log Management, i.e. i have to lookup on some setup table and every time the user inserts, updates or deletes a record, if the record is from the table in the setup then do something.
It's just like Change Log Management.
I can put triggers on all the tables that i need to, but i want to do this with some kind of configuration/setup table.
So actually, the question is how can i catch insert, update and delete actions? Is there a codeunit or something that is executed every time the users inserts, updates or deletes record?
I'am using Client Monitor to find out how Change Log is working, but i can not conclude anything.

Any suggestion would be helpful.

Oh, and we are using NAV 5.0 SP1 on MSSQL Server 2005.

thank you

kapamarou

Do you need something extra from the change log managment and you are developing something? Doesn't it cover your needs?

Look in Codeunit 1 "Application Managment" triggers OnGlobalInsert,OnGlobalModify,OnGlobalDelete,OnGlobalRename. They are executed when the user modifies something.

matttrax

skalp wrote:

It's just like Change Log Management.

If it's just like the change log, why are you coding it from scratch?

skalp

Hi,

Thank you for your replies.
The idea is that the table that i need are different from the tables in the Change Log.
Change Log will continue to work, but i need to detect insert, update and delete in some of the tables that i configure/setup.
I will not do logging, i will insert records in the table for integration with other system.
That is why i need to now where i can catch those 3 actions.

In Codeunit 1 "Application Managment" triggers OnGlobalInsert,OnGlobalModify,OnGlobalDelete,OnGlobalRename are executing only if table is defined in the Change Log Setup.

thank you all

matttrax

Have you considered using SQL to fill in the tables in the NAV database or even a completely new database?

skalp

matttrax wrote:

Have you considered using SQL to fill in the tables in the NAV database or even a completely new database?

I can use SQL, write triggers on all the tables, but you see, i want some functionality that i can configure, something exactly like Change Log, just to put tables that i want to integrate than monitor all inserts, updates and deletes, and check, if the records belong to the specified table the i will insert the record in the integration table, or maybe different database.

Maybe i did not get your idea well?

thank you

matttrax

Just that monitoring every insert / modify / delete is a lot of overhead on the database. If you don't need real time data you could use SSIS or any number of data movement tools to populate an outside database and use that for your integration.

kine

Do not forget that the Change Log will not log the changes made by code, it is logging only changes made by user!. If you want to have all changes catched, think about replications and trigger modifications...

skalp

kine wrote:

Do not forget that the Change Log will not log the changes made by code, it is logging only changes made by user!. If you want to have all changes catched, think about replications and trigger modifications...

Yes, I'am aware of this, but i'am not touching change log functionality, this will continue to work as it is.

If there is no other solution, I will after all create triggers on every table that i need, but you now every time when i want to monitor additional table i will have to write triggers to that table all over again. The idea is to create a setup table and only to check table and fields that i want to manipulate with, that is exactly what Change Log Management does, and that was my motivation to search a solution like this.

I need this for synchronization purposes, so it must be done online, as it happens, so every change to the NAV must be transfered to other system. Basically it will be transfered to sync table and then pulled by other program.

thank you

kine

As I wrote - if you use Transaction replication, you will have Stored procedures for Deletion, Insert and Modify automatically and you can expand them if you want by code which will do what you need. CONS is that you have additional database, PROS is that you can easilly react to the change and you do not have impact to the NAV DB.

Standard Outlook synchronization in NAV is using Change Log for tracking changes which needs to be replicated.

skalp

kine wrote:

As I wrote - if you use Transaction replication, you will have Stored procedures for Deletion, Insert and Modify automatically and you can expand them if you want by code which will do what you need. CONS is that you have additional database, PROS is that you can easilly react to the change and you do not have impact to the NAV DB.

Standard Outlook synchronization in NAV is using Change Log for tracking changes which needs to be replicated.

You're right, new database is a solution, i agree, we will examine this idea.
But still in NAV 5.0 there is no chance that we can capture those events.

So, the conclusion is that this can be done with Replication database and/or triggers on tables witch maybe after all is not such a bad thing

Thank you again kine, all

kine

Plus of this is, that the "triggers" are in the replicated database (you will not mess NAV data and not loose something when importing objects etc.) and they are defined as stored procedures, not table triggers. Thus you can manage them from one place etc. And the replicated DB could be in your DMZ and you do not need to allow access for the external application into NAV DB...

skalp

kine wrote:

Plus of this is, that the "triggers" are in the replicated database (you will not mess NAV data and not loose something when importing objects etc.) and they are defined as stored procedures, not table triggers. Thus you can manage them from one place etc. And the replicated DB could be in your DMZ and you do not need to allow access for the external application into NAV DB...

Yes, i agree with you, we will examine this definitely.
We have no experience with replication but it should'nt be hard.

So, i guess that i can mark this as solved

thank you all,