插入重复记录不起作用的触发器 sql 服务器

Question

ALTER TRIGGER [dbo].[STOK_HARKETLERI_Insert]    
ON [dbo].[STOK_HAREKETLERI]              
FOR INSERT 
AS BEGIN  
    declare @tip int 
    declare @miktar float 
    declare @stokkod nvarchar 
    declare @tarih datetime 
    declare @counter int  
    Select 
        @tip = sth_tip,  @miktar = sth_miktar,  
        @stokkod = sth_stok_kod, @tarih = sth_tarih 
    from inserted 
    select @Counter = COUNT(sth_tip) 
    from STOK_HAREKETLERI  
    where sth_evraktip = 6 
      and sth_tip = @tip 
      and sth_miktar = @miktar 
      and @stokkod = sth_stok_kod 
      and @tarih = sth_tarih   
    if (@counter>=1)  
    begin     
       rollback     
       RAISERROR ('Record already exists', 17, -1) with log 
    end    
END
GO

触发器未在插入语句上触发，但是如果我删除变量并填充数据并在 SQL Server 上运行它，它运行良好。

有什么建议吗？

如果我将行(@counter >= 1)更改为(@counter >= 0)它又开始工作，还有一件事。

Answer 1

如果您插入多行，则"已插入"中将有多个行，但您只检查最后一行。根据有关"sth_evraktip = 6"的规则实际是什么，进行检查约束可能更容易（以后更新后是否可以完成更多行等）。

使用插入触发器，这样的东西可能会起作用：

if exists (select 1 from inserted i
where exists(select 1 from STOK_HAREKETLERI S
where S.sth_evraktip = 6
and S.sth_tip = i.sth_tip
and S.sth_miktar = i.sth_miktar
and S.sth_stok_kod = i.sth_stok_kod
and S.sth_tarih = i.sth_tarih
and S.sth_RECno < i.sth_RECno)) begin
    rollback
    RAISERROR ('Record already exists', 17, -1) with log 
 end

如果任何列可以包含 NULL，则必须添加更多逻辑来处理该列。

Answer 2

如果我跳过变量声明并将值传递给变量触发器可以完美运行。对于我的每一行编辑后的代码发布在下面。

Create TRIGGER [dbo].[STOK_HARKETLERI_Insert]
   ON  [dbo].[STOK_HAREKETLERI]
    FOR INSERT
AS
BEGIN
Declare @counter int
select @counter =  COUNT(sth_tip) from STOK_HAREKETLERI
where sth_evraktip = 6
and sth_tip = (select sth_tip from inserted i)
and sth_miktar =(select sth_miktar from inserted)
and sth_stok_kod =(select sth_stok_kod from inserted)
and sth_tarih = (select sth_tarih from inserted)
and sth_RECno < (select sth_RECno from inserted)
if (@counter>=1)
begin
    rollback
    RAISERROR ('Record already exists', 17, -1) with log
end

END