Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table master_bill
(
    id bigint not null primary key,
    mb_no bigint not null,
    created_by text not null,
    updated_by text not null,
    created_at timestamp with time zone default now() not null,
    updated_at timestamp with time zone default now() not null
);


create table order_info
(
    order_id bigint not null primary key,
    mb_id bigint not null references master_bill(id) on delete cascade,
    created_by text not null,
    updated_by text not null,
    created_at timestamp with time zone default now() not null,
    updated_at timestamp with time zone default now() not null
);

create table master_bill_order_leg
(
    mb_id bigint references master_bill(id) on delete cascade,
    order_leg_id bigint,
    constraint master_bill_order_mb_id_order_leg_id_key unique (mb_id, order_leg_id)
);

create table order_leg
(
    id bigint primary key,
    created_by text not null,
    updated_by text not null,
    created_at timestamp with time zone default now() not null,
    updated_at timestamp with time zone default now() not null
);
CREATE OR REPLACE FUNCTION public.f_delete_order_leg()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
begin
	delete from order_leg where id in (select order_leg_id from old_table);
    
	return null;	
end;
$function$
;
create trigger t_delete_order_leg 
after delete
on master_bill_order_leg 
referencing old table as old_table
for each statement 
execute function f_delete_order_leg();
insert into master_bill values
(1,1,'john',''),
(2,2,'john','');

insert into order_info values
(1,1,'',''),
(2,1,'',''),
(3,2,'','');

insert into order_leg values 
(1,'',''),
(2,'',''),
(3,'','');

insert into master_bill_order_leg values 
(1,1),
(1,2),
(2,3);
2 rows affected

3 rows affected

3 rows affected

3 rows affected

select * from master_bill;
select * from order_info;
select * from order_leg;
select * from master_bill_order_leg;


id
mb_no
created_by
updated_by
created_at
updated_at


1
1
john

2022-04-27 20:41:17.914227+01
2022-04-27 20:41:17.914227+01

2
2
john

2022-04-27 20:41:17.914227+01
2022-04-27 20:41:17.914227+01




order_id
mb_id
created_by
updated_by
created_at
updated_at


1
1


2022-04-27 20:41:17.914227+01
2022-04-27 20:41:17.914227+01

2
1


2022-04-27 20:41:17.914227+01
2022-04-27 20:41:17.914227+01

3
2


2022-04-27 20:41:17.914227+01
2022-04-27 20:41:17.914227+01




id
created_by
updated_by
created_at
updated_at


1


2022-04-27 20:41:17.914227+01
2022-04-27 20:41:17.914227+01

2


2022-04-27 20:41:17.914227+01
2022-04-27 20:41:17.914227+01

3


2022-04-27 20:41:17.914227+01
2022-04-27 20:41:17.914227+01




mb_id
order_leg_id


1
1

1
2

2
3


delete from master_bill where id = 1
1 rows affected

select * from master_bill;
select * from order_info;
select * from order_leg;
select * from master_bill_order_leg;


id
mb_no
created_by
updated_by
created_at
updated_at


2
2
john

2022-04-27 20:41:17.914227+01
2022-04-27 20:41:17.914227+01




order_id
mb_id
created_by
updated_by
created_at
updated_at


3
2


2022-04-27 20:41:17.914227+01
2022-04-27 20:41:17.914227+01




id
created_by
updated_by
created_at
updated_at


3


2022-04-27 20:41:17.914227+01
2022-04-27 20:41:17.914227+01




mb_id
order_leg_id


2
3
id	mb_no	created_by	updated_by	created_at	updated_at
1	1	john		2022-04-27 20:41:17.914227+01	2022-04-27 20:41:17.914227+01
2	2	john		2022-04-27 20:41:17.914227+01	2022-04-27 20:41:17.914227+01