You can do with it triggers (but please read the whole answer)
SQL>
SQL> CREATE TABLE t
2 ( t_id number(5) ,
3 s_id number(5),
4 name char(20),
5 mgr_id number(5),
6 CONSTRAINT pk_t PRIMARY KEY(t_id, s_id)
7 );
Table created.
SQL>
SQL> insert into t values(1,12,'a',0);
1 row created.
SQL> insert into t values(2,12,'b',1);
1 row created.
SQL> insert into t values(3,11,'c',1);
1 row created.
SQL> insert into t values(4,11,'aa',2);
1 row created.
SQL> insert into t values(5,11,'d',4);
1 row created.
SQL> insert into t values(6,10,'e',null);
1 row created.
SQL> insert into t values(7,11,'ab',3);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> create or replace
2 trigger after_delete
3 after delete
4 on t
5 begin
6 lock table t in exclusive mode;
7 update t
8 set mgr_id = 0
9 where mgr_id not in ( select t_id from t );
10 end;
11 /
Trigger created.
SQL>
SQL> select * from t;
T_ID S_ID NAME MGR_ID
---------- ---------- -------------------- ----------
1 12 a 0
2 12 b 1
3 11 c 1
4 11 aa 2
5 11 d 4
6 10 e
7 11 ab 3
7 rows selected.
SQL> delete from t where t_id = 2;
1 row deleted.
SQL> select * from t;
T_ID S_ID NAME MGR_ID
---------- ---------- -------------------- ----------
1 12 a 0
3 11 c 1
4 11 aa 0
5 11 d 4
6 10 e
7 11 ab 3
6 rows selected.
So the above is the easiest of trigger solutions - we have to lock the entire table to ensure no-one adds new managers etc whilst we are doing are cleanup. But it will scan the whole table. If you want, you could add some complexity to only consider the manager id's in question
SQL>
SQL> rollback;
Rollback complete.
SQL>
SQL> drop trigger after_delete;
Trigger dropped.
SQL>
SQL> create or replace trigger after_delete
2 for delete on t
3 compound trigger
4
5 mgr_list sys.odcinumberlist := sys.odcinumberlist();
6
7 before each row is
8 begin
9 mgr_list.extend;
10 mgr_list(mgr_list.count) := :old.t_id;
11 end before each row;
12
13 after statement is
14 begin
15 lock table t in exclusive mode;
16 update t
17 set mgr_id = 0
18 where mgr_id in ( select column_value from table(mgr_list) );
19
20 end after statement;
21
22 end ;
23 /
Trigger created.
SQL>
SQL> select * from t;
T_ID S_ID NAME MGR_ID
---------- ---------- -------------------- ----------
1 12 a 0
2 12 b 1
3 11 c 1
4 11 aa 2
5 11 d 4
6 10 e
7 11 ab 3
7 rows selected.
SQL> delete from t where t_id = 2;
1 row deleted.
SQL> select * from t;
T_ID S_ID NAME MGR_ID
---------- ---------- -------------------- ----------
1 12 a 0
3 11 c 1
4 11 aa 0
5 11 d 4
6 10 e
7 11 ab 3
6 rows selected.
But to be honest, this strikes me as a more of a data modelling issue. It would seem to me that a correction to your data model for the primary key would be a better option. Then a simple 'on delete set null' plus a virtual column should do the trick
SQL>
SQL>
SQL> drop table t cascade constraints purge;
Table dropped.
SQL>
SQL> CREATE TABLE t
2 ( t_id number(5) ,
3 s_id number(5),
4 name char(20),
5 mgr_id number(5),
6 CONSTRAINT pk_t PRIMARY KEY(t_id),
7 constraint fk foreign key ( mgr_id ) references t ( t_id ) on delete set null
8 );
Table created.
SQL>
SQL>
SQL>
SQL> insert into t values(1,12,'a',null);
1 row created.
SQL> insert into t values(2,12,'b',1);
1 row created.
SQL> insert into t values(3,11,'c',1);
1 row created.
SQL> insert into t values(4,11,'aa',2);
1 row created.
SQL> insert into t values(5,11,'d',4);
1 row created.
SQL> insert into t values(6,10,'e',null);
1 row created.
SQL> insert into t values(7,11,'ab',3);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> select * from t;
T_ID S_ID NAME MGR_ID
---------- ---------- -------------------- ----------
1 12 a
2 12 b 1
3 11 c 1
4 11 aa 2
5 11 d 4
6 10 e
7 11 ab 3
7 rows selected.
SQL> delete from t where t_id = 2;
1 row deleted.
SQL> select * from t;
T_ID S_ID NAME MGR_ID
---------- ---------- -------------------- ----------
1 12 a
3 11 c 1
4 11 aa
5 11 d 4
6 10 e
7 11 ab 3
6 rows selected.
SQL> alter table t add mgr_disp number as ( nvl(mgr_id,0));
Table altered.
SQL> select * from t;
T_ID S_ID NAME MGR_ID MGR_DISP
---------- ---------- -------------------- ---------- ----------
1 12 a 0
3 11 c 1 1
4 11 aa 0
5 11 d 4 4
6 10 e 0
7 11 ab 3 3
6 rows selected.
SQL>
SQL>
SQL>