触发检查重复输入 [英] trigger for checking duplicate entry

问题描述

大家好，再次回来，这次是SQL问题。



我有以下SQL触发器来防止重复输入。

Hi everyone, back again, this time with SQL question.

I have the following SQL trigger to prevent duplicated entry.

go
create trigger insAssset
on tblAssets
for insert
as

begin 
		
		if exists(
		
			select A.ARNumber
			from tblAssets as A
			inner join inserted as N
			on A.AssetID=N.AssetID
		where
			A.ARNumber=N.ARNumber
		)		
			begin
			print 'The asset already exist in the database'
			rollback transaction
			return
			end
		else 
			commit transaction 
			print 'Asset added to the database.'
		
end	

但是，此触发器会阻止任何类型的数据库进入，即使记录没有存在。

我做错了什么？非常感谢任何建议。

However, this trigger prevent any kind of entry into the database, even if the record doesn't exists.
What am I doing wrong? any suggestion would be very much appreciated.

推荐答案

您正在加入AssetID上的插入表，这意味着您不仅希望看到ARNumber是唯一的，而且AssetID必须匹配。



最简单的做法是不使用触发器，只是在列上放置一个唯一约束。然后SQL为你处理它。



一个很好的一般规则是，如果你认为你必须使用触发器，你可能会错过一个更好的设计决定。



这是触发器的错误类型，因为触发器应支持使用集合，这意味着您插入的表中可能有20条记录，因此检查if exists将影响插入表中的所有20条记录。



使用唯一约束。

You're joining to the inserted table on AssetID which means you aren't just looking to see that ARNumber is unique but that there must be a match for AssetID.

The easiest thing to do is not use triggers and just put a unique constraint on the column. Then SQL handles it for you.

A good general rule is if you think you have to use a trigger, you may be missing a better design decision.

This is the wrong type of things for triggers because a trigger should support working with sets, meaning that your inserted table could have 20 records in it so checking for "if exists" will affect all 20 records in the inserted table.

Go with the unique constraint.

这篇关于触发检查重复输入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！