how to monitor changes on tables automatically

[Dark Smile]

Hello,


I like to do the following:

whenever a field is created on a table (or deleted or some properties changed), i will read the userId of the current user and the company he do the changes in.

So far i tried to work with the class SysDictField\new and added a Info() but in this class i can't get the tableId and fieldId of the changed object.
Same problem on class ReleaseUpdateDB and SysDictTable.
(The System runs through these classes whenever the described action is performed)

My next Idea was to get the information out of table UtilIdElements but this Table is not in the SQL Database so i can't set a trigger to it to monitor changes.

The MorphX VCS can't be used here because i don't want to check in / out the objects manually.

So anyone has an idea how to manage that?


And I'm sorry for the bad english :S

Kind Regards
Robin

[gl00mie]

Well... you can set a trigger on the SqlDictionary table insert/delete to get an alert when a new field (or table/view!) is kinda created "physically" in the database (or when a table/view/field is dropped). In a database trigger you'll have to somehow connect to an AOS, enumerate user session objects and find an information about a user session that is currently using the database session in which a trigger is running (by the session SPID or something). Then you can find a record with this user session id in the SysClientSessions table and find out the userId.
Note that this way you can't monitor changes that don't alter the database scheme, e.g. changes in a field's label or EDT.

[Dark Smile]

At first thank you for the answer.
The solution may work well no question but in my case i have to monitor property changes (label, mandatory, EDT) also.

Do you have any other idea or approach how to do that ?

Some more details:

i want to monitor whenever there are changes for a protokoll. Normally we comment our changes e.g. on classes or form methods. But on tablefields (and EDT's etc) it's difficult to insert a comment.
So for that i want to intervene in the insert/update/delete process so i get automatically a protokoll of the changes without doing anything manually expect the changes themselfs

[gl00mie]

I hardly think you can setup any kind of a trigger to monitor such changes. You can periodically copy current application to the old directory, then query for UtilElements for table fields that where changed since the last copying and compare such fields with the ones in the old layer to find out which properties have been changed... Or you can use a VCS.

[Dark Smile]

To copy the application directory is no solution because the size of it is more than 1 GB.

The MorphX VCS can't be used here (or better: i don't want to) because i have to check in / out the objects manually and my goal is to get that done automatically in the background

[S.Kuskov]

Цитата:

Сообщение от Dark Smile

To copy the application directory is no solution because the size of it is more than 1 GB.

Copy can be only one layer, which is being developed...
But it is no solution too because then need to restart AOS.

Maybe try catch needed events on GUI in property panel?

[gl00mie]

Цитата:

Сообщение от Dark Smile

To copy the application directory is no solution because the size of it is more than 1 GB.

So what? You can run this once per day: stop AOS, copy current application files to the old subdirectory, start AOS, run a report or something that will scan for changes since the last run (you can automate this ). As to triggers that would allow you to be alerted at the same moment when a changed/created field is saved - there are just no such hooks in the kernel, as far as I know.

[S.Kuskov]

Then it is easier create batch job witch makes check in / out the objects to VCS

[Alex_KD]

Цитата:

My next Idea was to get the information out of table UtilIdElements but this Table is not in the SQL Database so i can't set a trigger to it to monitor changes.

Try to stick with this one.
You should be able to get all AOT elements that were modified.

Please find the sample xpp code, which returns all elements that were modified in the last 5 minute (300 second), below:

 UtilIdElements          utilIdElements;
         utcdatetime        datetime;
         date   modifyDate;
         ;
      
          begin = WinApi::getTickCount();
          modifyDate = systemdateget();

          datetime = DateTimeUtil::newDateTime(modifyDate,  ((timenow() - 300) > 0)?(timenow() - 300):(0));
    
          while select name, id, recordType from utilIdElements
          where  (utilIdElements.createdDateTime > datetime) ||
               (utilIdElements.modifiedDateTime > datetime)
           {
                switch(utilIdElements.recordType)
                 {
                       ......
                 }
           }

[Logger]

Цитата:

Сообщение от Alex_KD

Try to stick with this one.
You should be able to get all AOT elements that were modified.

Please find the sample xpp code, which returns all elements that were modified in the last 5 minute (300 second), below:

 UtilIdElements          utilIdElements;
         utcdatetime        datetime;
         date   modifyDate;
         ;
      
          begin = WinApi::getTickCount();
          modifyDate = systemdateget();

          datetime = DateTimeUtil::newDateTime(modifyDate,  ((timenow() - 300) > 0)?(timenow() - 300):(0));
    
          while select name, id, recordType from utilIdElements
          where  (utilIdElements.createdDateTime > datetime) ||
               (utilIdElements.modifiedDateTime > datetime)
           {
                switch(utilIdElements.recordType)
                 {
                       ......
                 }
           }

It's correct only for build 1500.4570
see
kb2472202 The ChangedDate field and the ChangedTime field on a class are not updated when you change the class in Microsoft Dynamics AX 2009 SP1

[Alex_KD]

Цитата:

Сообщение от Logger

It's correct only for build 1500.4570
see
kb2472202 The ChangedDate field and the ChangedTime field on a class are not updated when you change the class in Microsoft Dynamics AX 2009 SP1

Thanks for pointing this out.
I think this piece of code is enough to get idea....well it might require some amendments as I just copied it from the old xpo.

[Dark Smile]

Thanks for this code, i will try to stick with this and will give feedback

[Dark Smile]

Hey guys, thanks for the quick help to all.

My solution is a Class, that is called in SysDictField every time a change occures and reads the Data out of table UtilIdElements such as in the hint of Alex_KD.

[Dark Smile]

I got a new problem:

I created a class that works fine with the monitoring but now my problem is the point to call this class.
I called the class from the SysDictField Class in the past but there is the problematic, that i haven't any info about the created elements in this stack.
So i have to select the records created in the last X seconds from Table UtilIdElements. But what if the user created new fields, forgets to save and then the timespan is over and i don't get the element if you understand what i'm trying to explain.

My new idea is to capture the save-Button Event. But i could not get into the toolbar, do you have any ideas how to break in this case ?

Thanks