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.
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.]
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.
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 ?
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.
Answers
OR you have no clues?
Blog - rockwithnav.wordpress.com/
Twitter - https://twitter.com/RockwithNav
Facebook - https://facebook.com/rockwithnav/
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.
[ 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.]
Try enable NAV log also and maybe put some debugger enabled ondelete() trigger of NAV
Try logging as suggested.
Do the shipped lines in the SO never get posted?? They are deleted on posting!
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.