create trigger [dbo].[tr_m_b_material_standard_relations_i] on [dbo].[t_m_b_material_standard_relations]
for insert
as
begin
if exists(select 1 from INSERTED _a WHERE  material_id IN (SELECT material_id FROM dbo.t_m_b_material_standard_relations))
         
    begin
        select @msg='物料不允许重复！'
        raiserror(@msg,16,1)
        rollback tran
        return
    end
end

--for insert，此时insert的记录在表中是已经可以选出来的了
create trigger [dbo].[tr_m_b_material_standard_relations_i] on [dbo].[t_m_b_material_standard_relations]
for insert
as
declare @msg varchar(100);
if (select count(1) from inserted i,t_m_b_material_standard_relations t
    where i.material_id=t.material_id)>1
    begin
        select @msg='物料不允许重复！'
        raiserror(@msg,16,1)
        rollback tran
        return
end
go

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

SQL code

--刚才那种写法多条一起插入时有问题，这样写吧
create trigger [dbo].[tr_m_b_material_standard_relations_i] on [dbo].[t_m_b_material_standard_relations]
for insert
as
declare @msg varchar(100);
if exists (select 1 from inserted i,t_m_b_material_standard_relations t
    where i.material_id=t.material_id
    group by i.material_id
    having count(1)>1)
    begin
        select @msg='物料不允许重复！'
        raiserror(@msg,16,1)
        rollback tran
        return
end
go

------解决方案--------------------
inserted/deleted两个表存在于触发器内。所以你查不了。你可以在触发器里面select出来或者保存到一个实体表中再检查。

------解决方案--------------------
探讨

SQL code

--刚才那种写法多条一起插入时有问题，这样写吧
create trigger [dbo].[tr_m_b_material_standard_relations_i] on [dbo].[t_m_b_material_standard_relations]
for insert
as
declare @msg varchar(100);
if exists (se……