Set Blocked field via direct SQL

BrentNewbury

Hi all,

Does anyone see a problem with updating the 'Blocked' field in the Customer table using a SQL command directly on the database, rather than using C/Front?

We've analysed the SQL Server Profiler logs of setting the blocked status of a customer in NAV and then closing the customer record and noted that the only update statement executed was to update the blocked and last modified column. Additionally, we've done a data compare between a copy of the database before the flag was changed in NAV and after. Again, the only difference between the two databases being the blocked and last modified column.

Is there some C/Front trigger that doesn't exist in the database, but could possibly be triggered after updating the value? Are there any problems you know of as experts with doing this? Is there some audit trail that gets ammended (although we can't see that there is one being updated or added to)?

As a bit of added information, we plan on supporting this on Navision 4.0 and above.


Thanks for your time,

Brent Newbury

kriki

If there are no triggers in the OnValidate-trigger of the field or in the OnModify-trigger of the table or global triggers on that table, I don't see problems.
But that can always change with future versions.

BrentNewbury

Thanks for the quick response kriki. I'm not so worried about triggers on the database level, as those would be executed when the data changes (either directly or via C/Front). I'm more worried about any events that C/Front would raise if we were to execute the following code:

using (IDbCommand command = connection.CreateCommand())
{
	command.CommandText = "UPDATE [CRONUS International Ltd_$Customer] SET [Blocked] = 3, [Last Date Modified] = GETDATE() WHERE [No_] = 'CUST01'";
	
	command.ExecuteNonQuery();
}

As apposed to this C/Front .NET code:

int tableHandle = cfront.OpenTable(cfront.TableNo("Customer"));
int recordHandle = cfront.AllocRecord(tableHandle);
try
{
    cfront.SetFilter(tableHandle, cfront.FieldNo(tableHandle, "No."), "CUST01");

    if (cfront.RecordCount(tableHandle) > 0)
    {
        if (cfront.FindFirstRecord(tableHandle, recordHandle))
        {
            cfront.SetFieldData(
                tableHandle, 
                recordHandle, 
                cfront.FieldNo(tableHandle, "Blocked"), 
                NavisionFieldType.Option, 
                new NavisionOption(3).GetBytes() // Set Blocked status to 3 = All
                );

            cfront.ModifyRecord(tableHandle, recordHandle);
        }
    }
}
finally
{
	cfront.FreeRecord(recordHandle);
	cfront.CloseTable(tableHandle);
}

Does anyone foresee any problems with this? Apart from the obvious: building up the table names from the company name, while taking into account configured invalididentifierchars in all objects (table names and column names, etc).

kriki

1 problem : "Last Modified Date" is a date in NAV. In SQL it is a DateTime. You have to take the date-part of the GETDATE() to put in the field.

BrentNewbury

kriki wrote:

1 problem : "Last Modified Date" is a date in NAV. In SQL it is a DateTime. You have to take the date-part of the GETDATE() to put in the field.

Thanks for that kriki :thumbsup:. I wouldn't have known to do that.

If anyone can see any potential problems please shout up. I don't necessarily want a solution. Just point out a possible flaw in my understanding.