Oracle 11.2.0.4
Windows 64-bit client
I have a requirement to delete a row from a table if no child tables contain this data or if child data is present mark the row in the parent table as "inactive", change the description to 'BVobsolete'.
There are 45 - 50 child tables that can potentially contain this data.
Parent table definition:
CREATE TABLE s1_ship_mode
(
ship_mode_code varchar2(10) not null,
description varchar2(50) null,
sort_order number(5) not null,
inactive_flag char(1) DEFAULT 'N' not null
CONSTRAINT ckc_sm_inactive_flag CHECK (inactive_flag IN ('Y','N'))
using index
tablespace smartsoft_index
)
tablespace smartsoft_data
;
Sample data:
INSERT INTO s1_ship_mode (ship_mode_code,
description,
sort_order,
inactive_flag)
values ('T', 'Truck',1,'N');
INSERT INTO s1_ship_mode (ship_mode_code,
description,
sort_order,
inactive_flag)
values ('V, 'Vessel',2,'N');
INSERT INTO s1_ship_mode (ship_mode_code,
description,
sort_order,
inactive_flag)
values ('BV, 'Bulk Vessel',3,'N');
I thought I would use SQL like this:
BEGIN
DELETE FROM s1_ship_mode
WHERE ship_mode_code = 'BV';
EXCEPTION
WHEN OTHERS THEN
UPDATE s1_ship_mode
SET ship_mode_code = 'BVobsolete',
inactive_flag = 'Y'
WHERE ship_mode_code = 'BV';
END;
/
I would like to use a "data_found" exception but there is no such exception - only "no_data_found"
Is there a better solution than the one I have suggested??