A doctor who leaves the clinic cannot be deleted: appointments, visits, and invoices refer to the row, and the database refuses with ORA-02292. Yet the doctor should disappear from the lists. The answer is a soft delete: mark the row inactive instead of removing it.
This guide shows how to implement a soft delete in Oracle Forms 14.1.2 with an ON-DELETE trigger, so users keep pressing Delete Record as usual, plus a check box that shows the inactive rows again.
Sample Form for This Guide
The examples and screenshots use the sample form CH39_DOCTORS from the Oracle Forms code repository on GitHub. Download it, open it in Forms Builder, and connect as CAREWELL to follow along.
| Form | File | What it shows |
|---|---|---|
| CH39_DOCTORS | forms/ch39/ch39_doctors.fmb | Doctors marked inactive instead of deleted |
The forms run against the CareWell Clinic sample schema, which you install first.
Replace the DELETE Statement
When the user deletes a record and saves, Forms issues a DELETE for it. An ON-DELETE trigger on the block replaces that statement with your own code.
Example (ON-DELETE trigger on block DOCTORS):
-- a deleted doctor is only marked inactive: appointments, visits, and invoices still refer to the row update doctors set active = 'N' where doctor_id = :doctors.doctor_id;
Forms still does everything else as usual: it removes the record from the block, counts it, and commits. Only the statement changes, so the row stays in the table with ACTIVE set to N.
In a test, deleting a doctor and saving reported the normal message.
Output:
FRM-40400: Transaction complete: 1 records applied and saved.
ON-DELETE is one of the transactional triggers, which are covered in how to base a block on transactional triggers in Oracle Forms.
Show Active Rows Only
Set the block's WHERE Clause property to active = 'Y' in Forms Builder, so inactive doctors are not queried. The soft-deleted doctor is gone from the list after the next query, as if the row had been deleted.
Show Inactive Rows on Request
Sometimes users need the inactive rows back, for example to reactivate someone. A check box in the control block, Show inactive doctors, switches the block's WHERE clause and queries again.
Example (WHEN-CHECKBOX-CHANGED trigger on CTL.SHOW_INACTIVE):
-- (setting DEFAULT_WHERE to null left the block's WHERE clause in place: give a condition instead)
set_block_property('DOCTORS', DEFAULT_WHERE,
case :ctl.show_inactive when 'Y' then 'active in (''Y'', ''N'')' else 'active = ''Y''' end);
go_block('DOCTORS');
execute_query;Why not set DEFAULT_WHERE to null to show every row? A test showed that null left the block's WHERE clause as designed, active = 'Y', so the inactive doctors did not appear. The trigger gives a condition that is true for every row instead.
Reactivate a Row
A Reactivate button sets the flag back for the current doctor and saves.
Example (WHEN-BUTTON-PRESSED trigger on CTL.REACTIVATE):
if :doctors.active = 'N' then
:doctors.active := 'Y';
commit_form;
else
message('Dr ' || :doctors.last_name || ' is active.');
end if;Other Uses of the Same Idea
Replacing Forms' own statements works for more than deletes:
- An ON-UPDATE trigger that inserts a new version of the row instead of changing it keeps a full history.
- ON-INSERT, ON-UPDATE, or ON-DELETE can send the change to a stored procedure instead of the table.
Conclusion
A soft delete in Oracle Forms keeps the user's Delete Record and changes what Forms does with it: an ON-DELETE trigger updates an active flag instead of deleting the row. Filter the block with active = 'Y', switch DEFAULT_WHERE to a condition that is always true to show inactive rows, and give users a way to reactivate a row.
