Change Log - Custom SQL Triggers

Benno · 2007-02-02

Records are only inserted in the Change Log Entry table by userinteraction within Navision forms and not by codeunits or dataports. Could anybody tell me if they have some bad experience with creating custom Insert, Update and Delete triggers directly in SQL Server to fill a Navision table. This isn't a very nice solution because it isn't possible to maintain the custom SQL triggers within Navision but it seems like a could alternative to log changes in tables made by codeunits or dataports.

Waldo · 2007-02-02

Well,
I know that someone used SQL triggers to lock and unlock objects to be able to work in with several developers in one database (Ara3n was it?).

We are using SQL triggers to fill a temp NAV table to synchronize a planning tool with NAV.

So, imho, I don't think it's a bad idea. The only thing I wouldn't do is to directly insert into a NAV table. I would use a custom temp table, and use a batch job (NAS?) from within NAV to import it in the NAV tables... . This to ensure all validation is done well.

Phenno · 2007-02-02

Waldo wrote:

Well,
I know that someone used SQL triggers to lock and unlock objects to be able to work in with several developers in one database (Ara3n was it?).

We are using SQL triggers to fill a temp NAV table to synchronize a planning tool with NAV.

So, imho, I don't think it's a bad idea. The only thing I wouldn't do is to directly insert into a NAV table. I would use a custom temp table, and use a batch job (NAS?) from within NAV to import it in the NAV tables... . This to ensure all validation is done well.

One important thing. Once somebody changes something in that table (by replace or compiling, I'm not sure) you'll loose those triggers I think.

Test this situations:
Create triggers on table
Modify table (e.g. add one field) and compile it
Check your triggers
Import table with Merge option (Import some modified version, ofcourse)
Check your triggers
Import table with Replace option (Again, changed table)
Check your triggers

DenSter · 2007-02-02

Doing that on an existing NAV table is not a good idea, because NAV creates timestamp values (that are not really timestamp values) for each record that are needed to simulate the NAV versioning principle. Unless you can find out how that is done and do the same thing in your triggers, and you can also run NAV business logic on the tables that you intend to modify, I would not do it directly on SQL Server tables.

Waldo · 2007-02-02

Phenno wrote:

Waldo wrote:

Well,
I know that someone used SQL triggers to lock and unlock objects to be able to work in with several developers in one database (Ara3n was it?).

We are using SQL triggers to fill a temp NAV table to synchronize a planning tool with NAV.

So, imho, I don't think it's a bad idea. The only thing I wouldn't do is to directly insert into a NAV table. I would use a custom temp table, and use a batch job (NAS?) from within NAV to import it in the NAV tables... . This to ensure all validation is done well.

One important thing. Once somebody changes something in that table (by replace or compiling, I'm not sure) you'll loose those triggers I think.

Test this situations:
Create triggers on table
Modify table (e.g. add one field) and compile it
Check your triggers
Import table with Merge option (Import some modified version, ofcourse)
Check your triggers
Import table with Replace option (Again, changed table)
Check your triggers

Sorry, man, I'm afraid you're wrong.

To be sure, I tested your scenario ... triggers kept existing.

Also, how would our applications keep on working?

:-k

Phenno · 2007-02-02

Waldo wrote:

Sorry, man, I'm afraid you're wrong.

To be sure, I tested your scenario ... triggers kept existing.

As I said, I'm not sure but why not to spend some time to check-it... I'm pretty sure that in 3.60 I was loosing some triggers on such (or similar situations, was long time ago so I forgot).

Or that were some properties (NULL, I think) on fields that I had to add manually through SQL :-k

Phoooh, anyhow, whatever you plan to do through SQL Manager, you should make tests that I gave... Just to be sure.

Waldo wrote:

Also, how would our applications keep on working?

:-k

A possibility for making table in SQL server through Navision is good enough reason to not to play with tables with sql manager. That means that Navision executables has prepared SQL statements that could override your "manual" modifications to table. Only thing is to find which one will be overridden and wich one not.

davmac1 · 2007-02-02

I just tried an update trigger on the contact table where the only thing I am doing is writing changes to a custom table.
Navision would not let me make any changes to the contact record - another user has already changed....
I think the timestamp must be changing even though I am not changing any contact fields in the trigger.

I was going to ask about this at Convergence, but if anyone has any ideas now, I would love to hear them.

ara3n · 2007-02-02

Hello Davmac

Are you modifying the existing record?

your trigger should look something like this

DECLARE Contact_Record CURSOR LOCAL FOR
select [No_],[Name] from inserted

The Contact_Record would be the recordset that is being modified. You can then do what you need to do.

If you need additional fields, add them after the name

select [No_],[Name],[Name 2]  from inserted

Then

OPEN Contact_Record
FETCH Next from Contact_Record
INTO @v0,@v2
CLOSE Contact_Record

Then write your insert statement

nisert into [Custom Table] values(null,@v1,@v2)

this should work.
Notice that null is used for timestamp fields in your custom table.

davmac1 · 2007-02-04

THANKS!!!
I was not changing the contact table in the trigger, but obviously itwas changing anyway.
I have posted below the non-working trigger followed by the working trigger. (I still have some cleanup work to do - but it works!)

non-working trigger

CREATE TRIGGER [ContactAuditLog] ON [dbo].[Bowers$Contact]
FOR UPDATE
AS
DECLARE @DATE datetime,
@USER varchar(50)
select @DATE = GETDATE()
select @USER = USER_NAME()

INSERT into dbo.AuditLog
(TableName, No_, ConsignmentDirector, UserID, LogDate,
OldConsignmentDirector)
SELECT 'Contact', inserted.No_, inserted.[Salesperson Code],
@USER, @DATE, deleted.[Salesperson Code]
from inserted inner join deleted on inserted.No_ = deleted.No_
where inserted.[Salesperson Code] <> deleted.[Salesperson Code]

working trigger

CREATE TRIGGER [ContactAuditLog] ON [dbo].[Bowers$Contact]
FOR UPDATE
AS
DECLARE @DATE datetime,
@USER varchar(50),
@v1 varchar(20),
@v2 varchar(20),
@v3 varchar(50),
@v4 datetime,
@v5 varchar(20)

select @DATE = GETDATE()
--select @USER = USER_NAME() -- returns dbo
select @USER = SYSTEM_USER -- returns Windows login

DECLARE Contact_Record CURSOR LOCAL FOR
SELECT inserted.No_, inserted.[Salesperson Code],
@USER, @DATE, deleted.[Salesperson Code]
from inserted inner join deleted on inserted.No_ = deleted.No_
where inserted.[Salesperson Code] <> deleted.[Salesperson Code]

OPEN Contact_Record
Fetch Next from Contact_Record
INTO @v1, @v2, @v3, @v4, @v5
CLOSE Contact_Record

IF @v1 is not null
INSERT into dbo.AuditLog
(TableName, No_, ConsignmentDirector, UserID, LogDate,
OldConsignmentDirector)
VALUES ('Contact', @v1, @v2, @v3, @v4, @v5)

ara3n · 2007-02-04

You are welcome.
Now you don't have to wait till next convergence.

kriki · 2007-05-14

[Topic moved from Navision forum to SQL General forum]

Feldballe · 2007-06-01

Some time ago I developed triggers inside sql-tables running NAV 3.70 b. I've experienced that the native backup procedure returns error messages about incorrect tableobjects. Can't remember the exact error message. But it didn't implicate the object compiling capabilities in Navision. So my experience is that it's only a problem, if you for some reason needs a native backup of data or database structure.

Change Log - Custom SQL Triggers

Comments

Categories