Removes a record from a record buffer and from the database.
Syntax
DELETE record
[ VALIDATE ( condition , msg-expression ) ]
[ NO-ERROR ]
|
-
record
- The name of a record buffer. You can delete a record only after it has been put into a
record buffer by a CREATE, FIND, FOR EACH, or INSERT statement.
If you define an
alternate buffer for a table, you can delete a record from that buffer by using the
name of the buffer with the DELETE statement.
To delete a record in a table
defined for multiple databases, you must qualify the record's table name with the
database name. See the Record phrase reference
entry for more information.
- VALIDATE ( condition, msg-expression )
- Use the VALIDATE option to specify a logical value that allows the deletion of a
record when TRUE, but does not allow the deletion of a record when FALSE.
The
condition is a Boolean expression (a constant, field name,
variable name, or expression) with a value of TRUE or FALSE.
The
msg-expression is the message you want to display if the
condition is FALSE. You must enclose
msg-expression in quotation marks ("").
You can also
describe delete validation criteria for a table in the Data Dictionary. To suppress
the Data Dictionary delete validation criteria for a table, use the VALIDATE option as
follows:
If you use the DELETE statement to delete a record in a work table, the AVM
disregards any VALIDATE option you use with the DELETE statement.
- NO-ERROR
- Suppresses ABL errors or error messages that would otherwise occur and diverts them to
the ERROR-STATUS system handle. If an error occurs, the action of
the statement is not done and execution continues with the next statement. If the
statement fails, any persistent side-effects of the statement are backed out. If the
statement includes an expression that contains other executable elements, like methods,
the work performed by these elements may or may not be done, depending on the order the
AVM resolves the expression elements and the occurrence of the error.
To check for
errors after a statement that uses the NO-ERROR option:
- Check the ERROR-STATUS:ERROR attribute to see if the AVM raised the ERROR
condition.
- Check if the ERROR-STATUS:NUM-MESSAGES attribute is greater than zero to see if
the AVM generated error messages. ABL handle methods used in a block without
a CATCH end block treat errors as warnings and do not raise ERROR, do not set the
ERROR-STATUS:ERROR attribute, but do add messages to the ERROR-STATUS system handle.
Therefore, this test is the better test for code using handle methods without CATCH
end blocks. ABL handle methods used in a block with a CATCH end block raise
ERROR and add messages to the error object generated by the AVM. In this case, the
AVM does not update the ERROR-STATUS system handle.
- Use ERROR-STATUS:GET-MESSAGE( message-num ) to retrieve a
particular message, where message-num is 1 for the first
message.
If the statement does not include the NO-ERROR option, you can use a CATCH end
block to handle errors raised by the statement.
Some other important usage notes
on the NO-ERROR option:
- NO-ERROR does not suppress errors that raise the STOP or QUIT condition.
- A CATCH statement, which introduces a CATCH end block, is analogous to a NO-ERROR
option in that it also suppresses errors, but it does so for an entire block of
code. It is different in that the error messages are contained in a class-based
error object (generated by the AVM or explicitly thrown), as opposed to the
ERROR-STATUS system handle. Also, if errors raised in the block are not handled by a
compatible CATCH block, ON ERROR phrase, or UNDO statement, then the error is not
suppressed, but handled with the default error processing for that block type.
- When a statement contains the NO-ERROR option and resides in a block with a CATCH
end block, the NO-ERROR option takes precedence over the CATCH block. That is, an
error raised on the statement with the NO-ERROR option will not be handled by a
compatible CATCH end block. The error is redirected to the ERROR-STATUS system
handle as normal.
- If an error object is thrown to a statement that includes the NO-ERROR option,
then the information and messages in the error object will be used to set the
ERROR-STATUS system handle. This interoperability feature is important for those
integrating code that uses the traditional NO-ERROR technique with the newer,
structured error handling that features error objects and CATCH end blocks.
Examples
The r-delet.p procedure deletes all the records in the Customer
table.
r-delet.p
FOR EACH Customer:
DELETE Customer.
END.
|
The r-delet2.p procedure prompts the user for a Customer number and
then displays the name of that Customer. It then prompts the user to press y to confirm the
deletion of the Customer record. The user's response is stored in the del variable. If the
value of the del variable is y, the procedure deletes the Customer record.
r-delet2.p
DEFINE VARIABLE del AS LOGICAL NO-UNDO FORMAT "y/n".
REPEAT:
PROMPT-FOR Customer.CustNum.
FIND Customer USING Customer.CustNum.
DISPLAY Customer.Name.
del = NO.
UPDATE del LABEL "Enter ""y"" to confirm delete".
IF del THEN DELETE Customer.
END.
|
The r-delval.p procedure prompts the user for a Customer number. The
procedure displays the name of the Customer and prompts the user: Do you want to delete this
Customer? If the user answers no, the procedure prompts the user for another Customer
number. If the user answers yes, the procedure checks whether the Customer has orders, using
the VALIDATE option. If they do have orders, the procedure displays this message: This
Customer has outstanding orders and cannot be deleted. If the Customer has no orders, the
procedure deletes the Customer.
r-delval.p
DEFINE VARIABLE ans AS LOGICAL NO-UNDO.
REPEAT WITH 1 DOWN:
PROMPT-FOR Customer.CustNum.
FIND Customer USING Customer.CustNum.
DISPLAY Customer.Name.
ans = NO.
DISPLAY "Do you want to delete this Customer ?"
WITH FRAME f-query.
UPDATE ans WITH FRAME f-query NO-LABELS.
IF ans THEN
DELETE Customer VALIDATE(NOT(CAN-FIND(Order OF Customer)),
"This Customer has outstanding orders and cannot be deleted.").
END.
|
Notes
- When you run procedures that delete large numbers of records (for example, a month-end
table purge), the process runs much faster if you use the No Crash Protection (-i)
parameter in single-user mode. (You must back up your database before using this option.)
See OpenEdge Deployment: Startup Command and Parameter Reference for more
information on startup parameters.
- Deleting records does not change the amount of space the database takes up on the disk.
The AVM re-uses ROWIDs. It does not delete the ROWID when a record is deleted. To recover
disk space, you must dump and reload your database.
- The DELETE statement causes any related database DELETE triggers to execute. All DELETE
triggers execute before the AVM actually deletes the record. While a DELETE trigger is
executing, all FIND requests for the record (even within the trigger) fail, as if the
record were already deleted. If a DELETE trigger fails (or executes a RETURN ERROR
statement), the corresponding record is not deleted.
- If a table has both a DELETE trigger and delete VALIDATION, the DELETE trigger executes
before the validation is performed.
- If you have previously retrieved record with a field list, the DELETE
statement rereads the complete record before deleting it.
