CREATE trigGer [data_DDD] ON [dbo].[Data] After INSERT,UPDATE AS IF UPDATE (DataContent) BEGIN IF EXISTS (SELECT 1 FROM Data as t INNER JOIN Inserted as i ON i.DataContent = t.DataContent ) BEGIN raiserror('名称重复!',16,1) /*如果需要反馈请加上此行*/ ROLLBACK /*回滚*/ RETURN END END
alter trigGer [data_DDD] ON [dbo].[Data] after insert,UPDATE AS IF UPDATE (DataContent) BEGIN IF (select count(*) FROM Data where DataContent in(select DataContent from inserted))>=2 BEGIN ROLLBACK RETURN END END --问题出在你是after触发数据已经更新,此时那样查表无意思,肯定满足你的条件
楼上的朋友说得对,更正一下: CREATE trigGer [data_DDD] ON [dbo].[Data] After INSERT,UPDATE AS IF UPDATE (DataContent) BEGIN IF (SELECT count(*) FROM Data as t INNER JOIN Inserted as i ON i.DataContent = t.DataContent ) > 1 BEGIN raiserror('名称重复!',16,1) /*如果需要反馈请加上此行*/ ROLLBACK /*回滚*/ RETURN END END
ON [dbo].[Data]
After INSERT,UPDATE
AS
IF UPDATE (DataContent)
BEGIN
IF EXISTS (SELECT 1 FROM Data as t INNER JOIN Inserted as i
ON i.DataContent = t.DataContent )
BEGIN
raiserror('名称重复!',16,1) /*如果需要反馈请加上此行*/
ROLLBACK /*回滚*/
RETURN
END
END
IF UPDATE (DataContent)
BEGIN
IF (select count(*) FROM Data where DataContent in(select DataContent from inserted))>=2
BEGIN
ROLLBACK
RETURN
END
END --问题出在你是after触发数据已经更新,此时那样查表无意思,肯定满足你的条件
CREATE trigGer [data_DDD]
ON [dbo].[Data]
After INSERT,UPDATE
AS
IF UPDATE (DataContent)
BEGIN
IF (SELECT count(*) FROM Data as t INNER JOIN Inserted as i
ON i.DataContent = t.DataContent ) > 1
BEGIN
raiserror('名称重复!',16,1) /*如果需要反馈请加上此行*/
ROLLBACK /*回滚*/
RETURN
END
END