Changing Navision MS SQL table triggers

acio

Hi, all

our project leader (not Navision project) insist that we must change MS SQL (on which Navision server are working) tables triggers (or add) for ex. COMAPNYNAME$BA Db_ Virtual Cube Member.

Did anybody has an experience about that?
Or maybe arguments: why better don't do it?


Thanks

matttrax

I have a major argument against doing anything on the SQL backend unless absolutely necessary. I speak from experience. We have so many invalid records because others were deleted and business logic wasn't executed. It's ridiculous.

The major disadvantage is that the normal NAV programmer isn't going to know about that code. And odds are that the person who built that SQL code doesn't know NAV. So they don't work together correctly. Last time someone here did that they deleted the sales document with SQL during the middle of posting. Then the NAV code came along and tried to delete it. Funny how that makes it so that no one can post ](*,)

So in short, don't do it unless you have to (and you probably don't), in my opinion.

DenSter

I would like to know the reasons why that person thinks those changes have to be made. Generally speaking you don't touch anything that has anything to do with the objects of the database on SQL Server directly, you make all changes in C/SIDE.

acio

The reason is that we need to transfer information to onother DB (SapDB) and to the WebService which will transfer data (provisioning) to hardware. We wrote DLL (DataType OCX in Navision) which is working. She's argument - we nedd DLL on evvery workstation to be registred!

P.S. Moreover - is it posible from SQL trigger send signal to listener (Web Service)?

jlandeen

As already pointed out there can be some very dire consequences of updating and deleting Navision directly in tables from SQL.

However in a scenario you describe - where you are trying to integrate Navision with another external system I think it can be very benefitial to use SQL triggers to carry out certain actions. If you are using SQL 2005 then you can hook into .Net - which should pretty much allow you to write code to do anything, including invoking other web services.

I think this can be a very smart way of utilizing the capabilities of SQL 2005, .Net and Navision - but you do need to be careful in your implementation of a solution as you can very quickly corrupt or compromise your Navision system....and that's BAD!

jlandeen

wow it seems like there have been quite a few posts on some very similar topics. You may also want to see:http://www.mibuso.com/forum/viewtopic.php?f=33&t=31805 or http://www.mibuso.com/forum/viewtopic.php?f=33&t=31849.

bbrown

Are you talking about changing the table triggers that NAV creates (pre SP1) or adding your own triggers? Keep in mind that NAV will recreate those triggers anytime a change is made to the table.

DenSter

When I first read the topic I thought he meant that they want to modify the SIFT triggers, but it appears they have some sort of external app they want to connect with NAV.

Not only do those get redone every time the table is saved, the bigget danger is that SIFT totals may get screwed up, which may affect financial positions inside the application.

garak

acio wrote:

Hi, all
our project leader (not Navision project) insist that we must change MS SQL (on which Navision server are working) tables triggers (or add) for ex. COMAPNYNAME$BA Db_ Virtual Cube Member.

Why this? Whats the reason why you need to change the trigger in the tables that are made by Navision self?
If you change this triggers, as Daniel said, you became in trouble in the NAV application.
For example if you change the trigger for itemledgerentry that is fired to calculate the inventory and fill the SIFT tables (vers. 4), the inventory can be corrupted if you have some mistakes in the "redesigned" trigger.

You can create new triggers but not change the existing. also when you change in NAV for example the SIFTS, NAV will be redesign the self created triggers.

So, whats the reason that you need to change the triggers?