There was a discussion a while back in Known Issues about auditing users that have changed an object. If you’re running against SQL Server here is a solution to the issue of auditing changes to the Object table, that takes 10 mins to set up. This is not possible for the Navision Server platform. You cannot change the fields in the Object designer, since that is fixed. But you can make a separate C/SIDE table object that looks at a view of the Object table, and add the required audit information to the SQL Object table. You need to use the Query Analyzer (or other SQL tool) for this stuff. This works for Navision Financials 2.5, 2.6x, Navision Attain 3.0 and later, and for SQL Server 7.0 and 2000. Here are the steps: 1. Close any clients since changing the object table will cause an error if the client is open. 2. In Query Analyzer, change to the required database and issue the following SQL statements:
ALTER TABLE Object ADD [User ID] VARCHAR(30) NOT NULL CONSTRAINT ObjDefUserID DEFAULT ''
GO
ALTER TABLE Object DROP CONSTRAINT ObjDefUserID
GO
ALTER TABLE Object ADD CONSTRAINT ObjDefUserID DEFAULT UPPER(CAST(SUSER_SNAME() AS VARCHAR(30))) FOR [User ID]
GO
ALTER TABLE Object WITH NOCHECK ADD CONSTRAINT ObjChkUserID CHECK ([User ID] = UPPER(CAST(SUSER_SNAME() AS VARCHAR(30))))
GO
CREATE TRIGGER ObjTrgUpdUserID ON Object FOR UPDATE AS
UPDATE Object
SET [User ID] = DEFAULT
FROM INSERTED AS I
WHERE Object.[Type] = I.[Type] AND Object.[Company Name] = I.[Company Name] AND Object.[ID] = I.[ID]
GO
CREATE VIEW [Object Audit] AS
SELECT
CAST(timestamp AS TIMESTAMP) AS timestamp,
CAST(Type AS INTEGER) AS Type,
CAST([Company Name] AS VARCHAR(30)) AS [Company Name],
CAST(ID AS INTEGER) AS ID,
CAST([Name] AS VARCHAR(30)) AS [Name],
CAST(Modified AS TINYINT) AS Modified,
CAST(Compiled AS TINYINT) AS Compiled,
CAST([BLOB Reference] AS image) AS [BLOB Reference],
CAST([BLOB Size] AS INTEGER) AS [BLOB Size],
CAST([DBM Table No.] AS INTEGER) AS [DBM Table No.],
CAST([Date] AS DATETIME) AS [Date],
CAST([Time] AS DATETIME) AS [Time],
CAST([Version List] AS VARCHAR(80)) AS [Version List],
CAST([User ID] AS VARCHAR(30)) AS [User ID]
FROM Object
WHERE Type BETWEEN 1 AND 5
GO
- You now have your new User ID field that will contain the current user when an object is inserted or updated, even outside of the Navision client. You also have a new SQL view object that you can base a new C/SIDE table object on. This is a read-only object, since all changes MUST continue to go through the real Object table (CAST is being used for every column, to make computed columns, to achieve this. In SQL Server 2000 you could instead use an INSTEAD_OF trigger on the view that raises an error and therefore prevents modifications). 4. Create a new C/SIDE table. Include the fields from the Object table that you want, and exclude those that you do not want. E.g. (You must have the first 3):
Type Integer
Company Name Text30
ID Integer
Name Text30
Modified Boolean
Compiled Boolean
Date Date
Time Time
Version List Text80
User ID Text30
Create keys:
Type, Company Name, ID
Type, Name
Set the DataPerCompany table property to No. For Attain only, set the LinkedObject table property to Yes. Set the Editable field property to No for all fields. Save the table, giving a name that matches the view name, in my example: Object Audit. If all went well in Query Analyzer, Attain will just save the object or Financials will prompt to link to the view (say OK). 5. You now use the Object Audit C/SIDE table to see who has made changes to objects in the table designer, available in the User ID field. This field is guaranteed to be correct due to the default and trigger defined. 6. You can change all identifier names I have given, as required, and the Object table itself may be localised in your country. The length of the User ID field need not be 30. An extension to this is to have the trigger make an audit history in a separate table, including the object layout itself, so you have a full history of your objects. Hope this is of use, or at least serves as an example of the integration possible b Edited by - SNielsen on 2001 Jul 25 17:12:46