Trigger vs login problem

rixrix

Hello,

We observed strange situation.. One Item Card with existing ILE Entries was somehow deleted and [item No_] in ILE table is Blank.
Nobody (even not programmers) knows how it happened. Obviously if there is Ledger Entry, Item card is undeletable ....
Has somebody observed such problem?


But what I wanted to ask is:
I wanted to log all delete queries on Item table (not only that made from NAV).

I made (directly on SQL Server) trigger for Item table (on DELETE) which inserts some info rows into another table in different database (specially created for this purpose)

I set up all rights, created logins in security tab and so on, but the problem is that (when deleting item card in NAV) for some users it works perfectly but for some of them it gives: Combination of password and login is not valid.
(when I delete trigger, all works as usual)

Can you help me?

I may try to move "Dellog table" into NAV DB, but I wanted it separated

Thank you

kine

I think that the difference in the users for which it works and doesn't work is, that some are sysadmins and some aren't.

You can try to create stored procedure in the second DB to write the log, and execute this stored procedure from the trigger. You can then set the stored procedure as "Execute as ..." to execute it under some account, which have rights to write into the target table.

rixrix

Thank you very much.
Will try it.

If it works it will be more elegant solution for me.

David_Singleton

rixrix wrote:

Hello,

We observed strange situation.. One Item Card with existing ILE Entries was somehow deleted and [item No_] in ILE table is Blank.
Nobody (even not programmers) knows how it happened. Obviously if there is Ledger Entry, Item card is undeletable ....
Has somebody observed such problem?

Its not a problem, its a standard feature of Navision that has been there since the DOS version. Its quite normal.

rixrix

Unwanted removing data is Standard? and feature?
Does it happen accidentaly?
As we know nobody from users was doing anything what could do it.
And as I know also our NAV implementation should not do it (impl. company people said it :-).

+

kine:
I was thinking about it, but I am not sure if I can insert multiple rows through stored procedure.
The trigger is inserting all rows selected from virtual "deleted" table

kine

Call the SP for each row in the deleted table... ;-)

David_Singleton

rixrix wrote:

Unwanted removing data is Standard? and feature?
Does it happen accidentaly?
As we know nobody from users was doing anything what could do it.
And as I know also our NAV implementation should not do it (impl. company people said it :-).

Did you test it? Did you hit F4 on on item?

rixrix

Of course I tested it

I can not delete Item if there are ILE entries or if there are some Purchase/Sale documents (Item is being purchased/sold)
That's why we are confused.

kine

In standard NAV, you can delete the Item card, if there are no open documents for it (Sales, Purchase, BOM, Service, Req. journal, Prod. orders).

matteo_montanari

rixrix wrote:

Of course I tested it

I can not delete Item if there are ILE entries or if there are some Purchase/Sale documents (Item is being purchased/sold)
That's why we are confused.

Hi

You can delete an item when its ILE are all closed ("open" flag to false).

Bye

Matteo

rixrix

In normal NAV yes, but we have implemented some security which disallow this ....
And we tested that it works.
That's why it's so strange ... like some routine bypassed this, but we do not know which one and when ....
Programmers said, they do not see where in code it could be possible ...

:-(