Upon delete do not want to delete

jwilder

I have a custom table that has a status field on it (Open,Closed,Deleted).

When someone deletes one of these records I want to actually change the Status field to Deleted and not actually delete the record. I put my code on the OnDelete to modify the record but the OnDelete trigger always wants to delete the record. So I can modify it but then it just gets deleted anyway. The EXIT statement does not work on the OnDelete trigger of a table. Thats what I need.

Any thoughts on how to do this?

MarkHamblin

Don't think you can do that directly, but you can be sneaky about it:

• Show the user a temporary version of the table
• Override the triggers in the table to test whether it's the temp table or not:
  • OnModify, get the real record and update it
  • OnDelete, get the real record and mark it as deleted
  • OnInsert, simply insert a new record

Here's an example using the Item table. In OnDelete (first code to run):

rrRecRef.GETTABLE(Rec);
IF rrRecRef.ISTEMPORARY THEN BEGIN
  recItem.GET("No.");
  recItem.Description := 'DELETED';
  recItem.Blocked := TRUE;
  recItem.MODIFY;
  EXIT;
END;//if

In OnModify:

rrRecRef.GETTABLE(Rec);
IF rrRecRef.ISTEMPORARY THEN BEGIN
  recItem := Rec;
  recItem.MODIFY;
  EXIT;
END;//if

Etc. To test, just create a form with a temp record for Item and delete a few.

Dev

No need to do all this . If its a customised table and DELETE is not called from out side simply put code like this On DELETE

COMMIT will save your change of status and ERROR will stop its deletion


Deleted := TRUE;
MODIFY;
COMMIT;
IF Deleted THEN
ERROR('ok');

jwilder

Yup that works. I could even change the Error('ok') to Error('') so there is no message to to the user.

MarkHamblin

Yep - that's a way better (and more obvious) solution.

Marije_Brummel

Why do you want to do this? Who approved this design?

A status "Deleted"?

Imagine someone else but you having to do maintenance on this code.

Please reconsider this design and go to something more NAV-like.

kine

Dev wrote:

No need to do all this . If its a customised table and DELETE is not called from out side simply put code like this On DELETE

COMMIT will save your change of status and ERROR will stop its deletion


Deleted := TRUE;
MODIFY;
COMMIT;
IF Deleted THEN
ERROR('ok');

This is VERY DANGEROUS! If you have code calling the delete in middle of transaction, this will divide it. If there will be some error after this, it will not rollback correctly! This could totally mess your database!

Chinmoy

This is (as everyone said) a very very dangerous thing to do. Just as an alternative, you can create another table with a suffix or prefix as archive, like NAV has, Sales Header Archive, Sales Line Archive, etc. and put the deleted record into this when it gets deleted from the original table.

Dev

Yup, Design looks unhealty , but it depends upon requirement, if such requiremnt comes on mibuso that means the developer had already purt a lot of stuff from his side and even convencing the client. If not succedded then it comes to mibuso. If the developer still changes the design than it goods else pick one of the solution.

Rest if Rec is deleted then it is stoped to actualy get delete. I dont think it can mess some thing as it it on top of the delete triger.

Dev

David_Cox

You could do this easy at the form level, as the form delete accepts a boolean, it is safe and any code calls on the table will not cause problems?

Option 1:

Form - OnDeleteRecord() : Boolean
Status = Status::Deleted;
MODIFY;
EXIT(false);

Option 2
Another way at form level which makes it hard for the user to delete by mistake.
Set the form Property DeleteAllowed = No
Add a Button [ Delete ]

IF CONFIRM('Mark this record as deleted?',FALSE)THEN BEGIN
  //Close the Record 
  Status = Status::Deleted;
  MODIFY;
END;

Option 3:
A bit of both confirm or exit!

Form - OnDeleteRecord() : Boolean
IF CONFIRM('Mark this record as deleted?',FALSE)THEN BEGIN
  Status = Status::Deleted;
  MODIFY;
END;
EXIT(false);

Then you can write a report to delete records at table level, I would also change the option Deleted to Closed!

HTH

David