--1、建立一个触发器(推荐)
create trigger on p for delete
as   
delete from spj where pno = (select pno from deleted)
go
--执行删除
delete from p where pname='螺丝'

--2、级联删除
alter table p add constraint pk_p_id primary key (pno)
go
--为tb创建外健，并指定级联删除
alter table spj add constraint fk_spj_aid foreign key (pno) references p(pno) on delete cascade
go

------解决方案--------------------
建立测试数据
SQL code

if object_id('dbo.SPJ') is not null
    drop table dbo.SPJ;
go

if object_id('dbo.P') is not null
    drop table dbo.P;
go

create table dbo.P
(
    pno int not null primary key,
    pname nvarchar(20) not null
);
go

create table dbo.SPJ
(
    sno int not null primary key,
    pno int not null
);

insert into dbo.P
select 1, 'type-a' union all
select 2, 'type-b' union all
select 3, 'type-c';
go

insert into dbo.SPJ
select 1, 1 union all
select 2, 1 union all
select 3, 1 union all
select 4, 2 union all
select 5, 3 union all
select 6, 3;
go

------解决方案--------------------
建议用外键约束
先删除子表在删除父表

------解决方案--------------------

 ?个人建议用事务处理。

　
SQL code

begin tran trPDel
    delete from spj where pno = (select top 1 pno from p where pname='螺丝')
    delete from p where pname='螺丝'
    if @@error<>0
    begin
        rollback tran trPDel
        return
    end
    
commit tran trPDel