Progress
Language Tutorial
for Windows


Deleting Records

The process for deleting records is simple. The following list identifies the steps in the delete process:

  1. Find a record to delete.
  2. Upgrade to an EXCLUSIVE-LOCK.
  3. Use the DELETE statement.

Figure 8–15 shows the data movement for the DELETE statement.

Figure 8–15: Data Movement with the DELETE Statement

The figure may seem nonsensical. The DELETE statement doesn’t copy data to the database. What it does do is mark the record for deletion and release the record to the RDBMS for eventual removal from the database. You could say that DELETE sends instructions, not data, back to the database.

Again, the syntax is simple, although delete has a new useful option. This is the syntax for the DELETE statement.

SYNTAX
DELETE record [ VALIDATE ( condition , msg-expression ) ] 

The VALIDATE function accepts an expression and a message to check and make sure that the deletion is allowable according to your business rules.

Follow these steps for a demonstration of the DELETE statement:

  1. Open lt-08-05.p and run it. The following display appears:
  2. Choose the Prev button to get one of the records you created in the last exercise. Since the new records were created with high customer numbers, they appear last in the record list. (Cust-Num is the primary index.)
  3. Choose Delete Customer. An alert box appears, as shown:
  4. An alert box is a sophisticated alternative for sending messages to the status area. The status area is an extension of the window widget and so is the alert box.

    In event-driven programming, alert boxes ask questions of the user or present the user with important information. Since alert boxes are bearers of important news, they block the rest of the interface—like a dialog box—until the user responds to them.

    The reason that a major new component is introduced right in the middle of a discussion on the DELETE statement has to do with good programming style. Good event-driven programming style dictates that you should always have the user confirm deletion of important data with an alert box. "Managing Your Application," discusses how alert boxes work.

  5. Choose Yes to delete the record, or choose No to cancel the deletion.
  6. If you try to delete one of the records that come with the sports database, you may get alert boxes explaining why you can’t delete the record. These behaviors are established by database triggers in the schema. They are a way of enforcing business rules.

  7. Choose Exit, then press SPACEBAR to return to the Procedure Editor.

Here is the code that created the display:

lt-08-06.p
    /**********  DEFINE QUERY  **********/
    DEFINE QUERY Item-Query FOR Item.
    /**********  DEFINE VARIABLES  **********/
    DEFINE VARIABLE Current-Record AS ROWID. 
    /**********  DEFINE FORM  **********/
    {lt-08-f1.i}
    /**********  DEFINE TRIGGERS  **********/
   {lt-08-t1.i} {lt-08-t2.i} {lt-08-t3.i}
    ON CHOOSE OF btn-Delete
    DO:
/*1*/      MESSAGE "Do you really want to delete" Item.Item-Name "?"
             VIEW-AS ALERT-BOX QUESTION BUTTONS YES-NO UPDATE Answer.
/*2*/      IF Answer THEN DO:
             Current-Record = RECID(Item).
/*3*/        FIND FIRST Item WHERE RECID(Item) = Current-Record
             EXCLUSIVE-LOCK NO-ERROR.
/*4*/         IF AVAILABLE(Item) THEN DO:
                DELETE Item.
/*5*/             OPEN QUERY Item-Query FOR EACH Item NO-LOCK.
/*6*/              GET FIRST Item-Query.
                 DISPLAY Item.Item-Num Item-Name Price On-Hand Allocated
                     Re-Order On-Order Cat-Page Cat-Description 
                        WITH FRAME Frame1.
               END.
/*7*/          ELSE DO:
                FIND FIRST Item WHERE RECID(Item) = Current-Record
                    NO-LOCK.
                 MESSAGE "Record in use. Unable to update." 
                   VIEW-AS ALERT-BOX WARNING BUTTONS OK 
                      TITLE "Delete Error".
               END.
           END.
    END.
   /**********  MAIN LOGIC  **********/
   OPEN QUERY Item-Query FOR EACH Item NO-LOCK.
   GET FIRST Item-Query.
   DISPLAY Item.Item-Num Item-Name Price On-Hand Allocated Re-Order 
        On-Order Cat-Page Cat-Description WITH FRAME Frame1 USE-TEXT.
   ENABLE btn-Prev btn-Next btn-Update btn-Add btn-Delete btn-Exit 
        WITH FRAME Frame1.
   WAIT-FOR CHOOSE OF btn-Exit.
   CLOSE QUERY Item-Query. 

These notes help explain the code:

  1. A special VIEW-AS syntax on a MESSAGE statement reroutes a message from the status area to an alert box. The UPDATE option and variable captures the user’s response to the question.
  2. If the user answers YES, then the DELETE logic executes.
  3. This FIND statement upgrades the lock.
  4. If the record is available with an EXCLUSIVE-LOCK, then DELETE executes.
  5. Reopening the query rebuilds the results list without the newly deleted record.
  6. This statement resets the cursor to the first record.
  7. This logic executes if the record is unavailable for deletion.
  8. Practice Problems

    Problem 8-4: lt-08-s4.p

    Make a copy of your solution to problem 8-3. Add the ability to create new customer records and to delete customer records using the techniques covered in this chapter.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095