I have a client that wants to add some code to the SQL modify table trigger. After records are inserted in NAV, if a value changes, they want to write to another table to capture the history.
I know that we can do this NAV, but my question is there a problem with doing it on the table trigger? Any risks you can foresee?
Well issue there are many. The biggest one is just keeping it all under control. The scripts you create are not known to NAV, so if you modify a table, then NAV rebuilds the table, and you need to be aware to put the scripts back again. Of course all this is doable, but you need to weight the cost of maintenance vs the benefits. I assume the reason for not doing it in NAV is the performance overhead, since the Change Log can be a killer if over used. If its a big project with a FULL TIME SQL DBA on staff at the client, then you are probably OK, but if its a project where the SQL DBA is the IT guy who read the SQL books, then I would reconcider. Its not the sort of thing you want to be responsible for, since you have no real control over it.
If you keep the logic away from any NAV tables, no problem (besides potential performance issues, as table triggers are really bad for performance). Other than that, the same objection to any external tool writing directly to NAV tables. You run the risk of missing business logic, and SIFT totals that don’t add up correctly.
Thanks, Daniel and David.
Business Logic Spread out (I know, I warned them about that, they don’t seem to care)
Performance because table triggers are slow
Potential Loss of script if NAV rebuilds the table. This one I’m not sure about because I don’t think NAV ever rebuilds the tables, unless you are doing a rename, which they are not at risk of.
I thought of one other potential problem. Since the table they are writing to is outside of NAV, if they were to do a NAV backup and restore, they might not bring over the table as well. Therefore when they tried to modify a record again, it would error out. This error would not appear at compile time exacerbating the risk.
No I don’t think you’ll get errors, it will just miss the trigger, since it’s not part of the NAV table design. So in addition to a NAV backup/restore, you will also need to keep track of all trigger code, and make sure you incorporate that into new databases. Of course you can always backup/restore using SQL Server backups, that way it should all be included.
If they are aware of the business logic risk, make sure they understand that any issues coming out of it will be charged Time and materials, and it may be a good source of work for you 
LOL! I’m fairly wary of this approach - but I want to be able to give the client as many concrete reasons as possible. I wouldn’t wish this kind of work on anyone, least of all myself… perhaps a talented freelancer out there? Specializing in SQL?
< silence, crickets chirping >
I’vehad clients who’ve wanted to do something similar. I suggestion was to create another table in Navision that held the type of updates they wanted to track and Navision would post to that table. The external application would read the records out of that table and do what it needed to do.
Navision in SQL makes heavy use of triggers to deal with the SIFT tables. If you added a key or adjusted the SIFT configuration then you’d have to re-code your trigger. You’d have to document well and re-add the code during upgrades (which the merge toolkit wouldn’t likely find).
Adding your own table and having the external application do what it needs to do is virtually self documenting and is a safer way to implement (without going ahead and purchasing the tracking granule).