Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE policeman (policemanid  NUMBER, NAME VARCHAR2(100), BOSSID NUMBER);
INSERT INTO policeman VALUES (1,'POLICE1',5);
1 rows affected

INSERT INTO policeman VALUES (2,'POLICE2',5);
1 rows affected

INSERT INTO policeman VALUES (3,'POLICE3',6);
1 rows affected

INSERT INTO policeman VALUES (4,'POLICE4',6);
1 rows affected

INSERT INTO policeman VALUES (5,'BOSS1',7);
1 rows affected

INSERT INTO policeman VALUES (6,'BOSS2',8);
1 rows affected

INSERT INTO policeman VALUES (7,'BOSSOFBOSS1',NULL);
1 rows affected

INSERT INTO policeman VALUES (8,'BOSSOFBOSS2',NULL);
1 rows affected

SELECT * FROM POLICEMAN;


POLICEMANID
NAME
BOSSID


1
POLICE1
5

2
POLICE2
5

3
POLICE3
6

4
POLICE4
6

5
BOSS1
7

6
BOSS2
8

7
BOSSOFBOSS1
null

8
BOSSOFBOSS2
null


select policemanid from 
(select * from policeman
order by dbms_random.value)
where rownum =1;


POLICEMANID


7


create or replace package my_value_holder
as
policemanid number;
boss number;
end my_value_holder;
/
CREATE OR REPLACE TRIGGER SWITCH_BOSS_ROW_TRG BEFORE
    DELETE ON POLICEMAN
    FOR EACH ROW
DECLARE
    BOSS   NUMBER;
BEGIN
    BOSS := :OLD.BOSSID;
    IF ( :OLD.POLICEMANID = :OLD.BOSSID ) THEN
        SELECT
            POLICEMANID
        INTO BOSS
        FROM
            (
                SELECT
                    *
                FROM
                    POLICEMAN
                ORDER BY
                    DBMS_RANDOM.VALUE
            )
        WHERE
            ROWNUM = 1;

    END IF;

    MY_VALUE_HOLDER.POLICEMANID := :OLD.POLICEMANID;
    MY_VALUE_HOLDER.BOSS := BOSS;
END SWITCH_BOSS_ROW_TRG;
/
CREATE OR REPLACE TRIGGER SWITCH_BOSS_ST_TRG AFTER
    DELETE ON POLICEMAN
BEGIN
    UPDATE POLICEMAN
    SET
        BOSSID = MY_VALUE_HOLDER.BOSS
    WHERE
        BOSSID = MY_VALUE_HOLDER.POLICEMANID;

END SWITCH_BOSS_ST_TRG;
/
DELETE FROM policeman WHERE policemanid = 6;
1 rows affected

select * from policeman;


POLICEMANID
NAME
BOSSID


1
POLICE1
5

2
POLICE2
5

3
POLICE3
8

4
POLICE4
8

5
BOSS1
7

7
BOSSOFBOSS1
null

8
BOSSOFBOSS2
null
POLICEMANID	NAME	BOSSID
1	POLICE1	5
2	POLICE2	5
3	POLICE3	6
4	POLICE4	6
5	BOSS1	7
6	BOSS2	8
7	BOSSOFBOSS1	null
8	BOSSOFBOSS2	null