D_E_L_E_T_Campo de controleRequired
Logical-deletion flag. Blank means active row; asterisk means logically deleted row.
FOUNDATIONPublished
D_E_L_E_T_ field and logical deletion in Protheus
D_E_L_E_T_ = espaço → registro ativo | D_E_L_E_T_ = "*" → registro excluído logicamenteUnderstand how Protheus represents logical deletion in SQL databases and why queries must filter D_E_L_E_T_ on each table.
Overview
In Protheus, the standard deletion model is logical: the row remains physically stored, but the D_E_L_E_T_ field marks it as no longer considered by the application. Direct SQL queries must handle this explicitly, otherwise the database may return rows that Protheus treats as deleted. The historical ADVPL X SQL programming material also relates D_E_L_E_T_ to R_E_C_N_O_ and R_E_C_D_E_L_, DBAccess control fields used for ISAM compatibility and unique-key handling after logical deletion.
Syntax
D_E_L_E_T_ = espaço → registro ativo | D_E_L_E_T_ = "*" → registro excluído logicamenteParameters
R_E_C_N_O_Campo de controleOptional
Physical identifier used for positioning and Recno() compatibility in SQL environments.
R_E_C_D_E_L_Campo de controleOptional
Supports unique-key composition for logically deleted rows by receiving R_E_C_N_O_ on deletion.
Return value
Not a function; it is a persistence convention in Protheus database tables.
Simple query returning only active rows
#Include "TOTVS.ch"
#Include "TopConn.ch"
User Function ExDelLogico()
Local cAlias := GetNextAlias()
Local cQuery := ""
cQuery := "SELECT SA1.A1_COD, SA1.A1_LOJA, SA1.A1_NOME "
cQuery += "FROM " + RetSqlName("SA1") + " SA1 "
cQuery += "WHERE SA1.A1_FILIAL = '" + xFilial("SA1") + "' "
cQuery += "AND SA1.D_E_L_E_T_ = ' ' "
cQuery += "ORDER BY SA1.A1_COD, SA1.A1_LOJA"
cQuery := ChangeQuery(cQuery)
TCQUERY (cQuery) ALIAS (cAlias) NEW
While !(cAlias)->(Eof())
ConOut((cAlias)->A1_COD + " - " + (cAlias)->A1_NOME)
(cAlias)->(DbSkip())
EndDo
(cAlias)->(DbCloseArea())
ReturnExpected result
The query returns only active customers for the correct branch and closes the temporary work area.
JOIN filtering logical deletion on each table
cQuery := "SELECT SA1.A1_COD, SA1.A1_NOME, SE1.E1_NUM, SE1.E1_SALDO "
cQuery += "FROM " + RetSqlName("SA1") + " SA1 "
cQuery += "INNER JOIN " + RetSqlName("SE1") + " SE1 "
cQuery += "ON SE1.E1_FILIAL = '" + xFilial("SE1") + "' "
cQuery += "AND SE1.E1_CLIENTE = SA1.A1_COD "
cQuery += "AND SE1.E1_LOJA = SA1.A1_LOJA "
cQuery += "AND SE1.D_E_L_E_T_ = ' ' "
cQuery += "WHERE SA1.A1_FILIAL = '" + xFilial("SA1") + "' "
cQuery += "AND SA1.D_E_L_E_T_ = ' ' "Expected result
The filter is applied to both SA1 and SE1, avoiding a mix of active and logically deleted rows.
R_E_C_D_E_L_ and key reuse
// Conceito simplificado:
// CODIGO R_E_C_N_O_ D_E_L_E_T_ R_E_C_D_E_L_
// 001 1 "*" 1
// 001 2 " " 0Expected result
The logically deleted row remains stored, while R_E_C_D_E_L_ helps release the unique key for a new active insertion.
- Filter logical deletion on every Protheus table queried directly with SQL.
- Qualify D_E_L_E_T_ with the table alias.
- Use RetSqlName() and ChangeQuery() for physical names and database portability.
- Treat logical deletion as different from physical deletion.
- Forgetting D_E_L_E_T_ may return rows Protheus considers deleted.
- Filtering only the main table is not enough when joining Protheus tables.
- Physical DELETE directly in the database may break traceability and standard routines.
- Do not manually update D_E_L_E_T_, R_E_C_N_O_ or R_E_C_D_E_L_ without controlled validation.
Comments
There are no approved comments yet.
Sign in with Google or Microsoft to comment.