防止插入两列重复数据的触发器

问题描述：

我正在研究 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.

防止插入两列重复数据的触发器

相关推荐