A couple months ago, I was planning to use SSAS to analyze our sales invoice,
I was using database table trigger, so I donot need to requery the big data
I create a table destination, which input from CustInvoiceJour transaction (insert/update/delete)
what I did wrong is, I create the database destination using AX AOT
the problem is, we cannot easly insert the table that created from AOT using trigger,
it causes havoc on our system,
the error was:
[Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert the value NULL into column ‘RECID’, table ‘AXGLK_PROD.dbo.[your destination table]’; column does not allow nulls. INSERT fails.
and I need to remove these table (from AOT), and then, I remove my trigger script
what happened?
when you create a table from AOT, the AOT generate a RecId Column, DataAreaId Column, and partition Column (include a RecVersion Column CMIIW)
what would happen then, if you registered using AOT, this table should operated only on AOS, because they gonna fill those Column. and when we using trigger script on sql, we will not (and cannot) fille these Column because we donot know what value will be.
that’s why, when we inserted to these table using trigger, the column that should fill, will be filled null by our trigger, and when we have null value, specially on these column, our ax will shows an error, and we cannot continue the transaction
so, how should we do if we want to use trigger?
easy, there were 2 ways to creating a trigger concept in ax 2012:
- create the table not from aot, but from sql only, but remember, when you create table from sql, AOS will not read it
- create the table from aot, and you override the insert, delete, update method to insert the action onto the tables