Automatic deletion in NAV 2016

navuser1 · 2017-07-08

Dear Sir,

In a new Production Environment, the (only) Sales Lines get deleted automatically.
We can not find the reason. Any body have any idea regarding this kind of activity in NAV 2016 System.

Kindly inform.

Regards,
navuser1

einsTeIn.NET · 2017-07-09

Are you sure it gets deleted, or is it just hidden in the background?

RockWithNAV · 2017-07-10

At what time it's getting deleted??
OR you have no clues?

navuser1 · 2017-07-11

Thanks for your replies....

Record gets deleted from the table. I have checked & it's 100% correct.
Currently, I have no clues when it's happened.

I have investigated the SQL for the data deletion issue and come to know that the Sale Lines (Document Type =Order) are get deleted thru any kind of Transaction and the responsible user is NETWORK SERVICE (As all end users are logging into the NAV database using NavUserPassword Credential, so I can not the mark the right NAV User)

For the time being I have written a SQL trigger to prevent the data deletion from the Sales Line (Document Type =Order) table.

ALTER TRIGGER [dbo].[Preventdeletion] 
ON [dbo].[CompanyName$Sales Line] 
FOR  DELETE AS 
BEGIN
  DECLARE @Count int
  DECLARE @DocType int
  SET @Count = @@ROWCOUNT;
  IF EXISTS (Select 1 from deleted WHERE deleted.[Document Type]=1)
  BEGIN
  --
  IF @Count >= (SELECT rowcnt
   FROM sysindexes 
   WHERE ID = OBJECT_ID('[dbo].[CompanyName$Sales Line]'))
  BEGIN
   RAISERROR('Cannot delete all rows',16,1) 
   ROLLBACK TRANSACTION
   RETURN;
  END
  --
 END
END;
GO

[ Note : - The Client is using the Sales Order Screen to post Shipment Entry Only. They never post any kind of Invoice from the Sales Order Screen.]

nick_robbo · 2017-07-11

You could use an Event Subscription to Subscribe to any Delete on that table? You could then just log the current call stack and/or details on the current User ID etc. You could log this in a table, file or send an email.

txeriff · 2017-07-14

Strange...
Try enable NAV log also and maybe put some debugger enabled ondelete() trigger of NAV

navuser1 · 2017-07-15

Today, I got a strange error message(SQL trigger's error) during posting a Sales Invoice, and I'm shocked.

Juhl · 2017-07-15

Dont use sql triggers, use NAV.
Try logging as suggested.

Do the shipped lines in the SO never get posted?? They are deleted on posting!

navuser1 · 2017-07-19

partially shipment is there.

navuser1 · 2017-07-19

I have checked & compare the code between the two text objects files (Codeunit 80), found no such kind of things.

navuser1 · 2017-07-20

Hi nick_robbo,
Thanks for your reply.

Still found no proper solution...

I know that the writing a sql trigger couldn't solve my problem. But it helps to prevent the data lose.

I have found (strange) some clue, which may help others to identify the main/one of the main reason for this issue.

1. The sql error message comes when I'm trying to delete the the first "Blank" Line from the Sales Invoice, where Invoice Lines are made by "Get Shipment Lines" function.

## But the Strange matter is that it is not happening for all the cases.

2. The Second called of function named"DeleteSalesLines" in the "OnDelete()" Trigger of the Sales Header (ID 36) Table finds the Sales Line records with Document Type =Order.

## Code of "DeleteSalesLines" is the BASE code of NAV

My investigation is still going on. As per your suggestion, will "Event Subscription" helps me to prevent the data lose with my sql trigger?

Suppose there is any fault C/AL code (SalesLine.DELETE OR SalesLine.DELETEALL without having any trigger call) is executed in NAV, will "Event Subscription" prevent the data from deletion ?

Kindly reply.

afarr · 2017-07-20

If you export all the objects as text, you can search for SalesLine.DELETE . You should also search for something like "Record 37" to find all variables for that table, since some variables are called SalesLine, some SalesLine2, etc.