如果还是要用触发器的话,得修改一下触发器:CREATE TRIGGER student_insert ON student AFTER INSERT AS IF (SELECT count(stu_email) FROM student where stu_email in (select stu_email from inserted )) >=2 --个数大于等于2 ,说明导致了重复 BEGIN PRINT 'Inserting fail' ROLLBACK END ELSE BEGIN PRINT 'Inserting success' END
1. 因为使用的触发器类型是AFTER INSERT ,导致exists (SELECT stu_email FROM inserted a where a.stu_email in (select stu_email from student)) 恒为真了,因为刚刚插入进去了,总是找的到该条记录, 所以,可使用 FOR INSERT类型或INTEAD OF类型;
ON student
AFTER INSERT
AS
IF (SELECT count(stu_email) FROM student
where stu_email in (select stu_email from inserted )) >=2 --个数大于等于2 ,说明导致了重复
BEGIN
PRINT 'Inserting fail'
ROLLBACK
END
ELSE
BEGIN
PRINT 'Inserting success'
END
恒为真了,因为刚刚插入进去了,总是找的到该条记录,
所以,可使用 FOR INSERT类型或INTEAD OF类型;
2.这种重复性测试还是建议在前端调用写一个checkExists(),然后后台写一个Storeprocedure就不必使用TRIGGER了。