Options

Upon delete do not want to delete

jwilderjwilder Member Posts: 263
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?

Answers

  • Options
    MarkHamblinMarkHamblin Member Posts: 118
    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.
  • Options
    DevDev Member Posts: 74
    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');
  • Options
    jwilderjwilder Member Posts: 263
    Yup that works. I could even change the Error('ok') to Error('') so there is no message to to the user.
  • Options
    MarkHamblinMarkHamblin Member Posts: 118
    Yep - that's a way better (and more obvious) solution.
  • Options
    Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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.
  • Options
    kinekine Member Posts: 12,562
    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!
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Options
    ChinmoyChinmoy Member Posts: 359
    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.
  • Options
    DevDev Member Posts: 74
    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
  • Options
    David_CoxDavid_Cox Member Posts: 509
    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
    Analyst Developer with over 17 years Navision, Contract Status - Busy
    Mobile: +44(0)7854 842801
    Email: david.cox@adeptris.com
    Twitter: https://twitter.com/Adeptris
    Website: http://www.adeptris.com
Sign In or Register to comment.