防止插入两列重复数据的触发器 [英] Trigger to prevent Insertion for duplicate data of two columns
本文介绍了防止插入两列重复数据的触发器的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在研究 SQL Server 2008R2
,我有下表
I am working on SQL Server 2008R2
, I am having the following Table
ID Name date
1 XYZ 2010
2 ABC 2011
3 VBL 2010
现在我想阻止插入,如果我有数据,尽管 ID 不同但数据存在
Now i want to prevent insertion if i have a Data although the ID is different but data is present
ID Name date
4 ABC 2011
请指导我如何编写这个触发器.
Kindly guide me how should i write this trigger.
推荐答案
是这样的:
CREATE TRIGGER MyTrigger ON dbo.MyTable
AFTER INSERT
AS
if exists ( select * from table t
inner join inserted i on i.name=t.name and i.date=t.date and i.id <> t.id)
begin
rollback
RAISERROR ('Duplicate Data', 16, 1);
end
go
这只是用于插入,您可能也需要考虑更新.
That's just for insert, you might want to consider updates too.
更新
一种更简单的方法是在表上创建一个唯一约束,这也将强制执行它进行更新并消除对触发器的需要.就这样做:
A simpler way would be to just create a unique constraint on the table, this will also enforce it for updates too and remove the need for a trigger. Just do:
ALTER TABLE [dbo].[TableName]
ADD CONSTRAINT [UQ_ID_Name_Date] UNIQUE NONCLUSTERED
(
[Name], [Date]
)
然后你就可以做生意了.
and then you'll be in business.
这篇关于防止插入两列重复数据的触发器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文