一个触发器与插入的问题

[l564400632]

CREATE DATABASE s;
USE s;
CREATE TABLE student
(
stu_ID VARCHAR(8)PRIMARY KEY,
stu_name VARCHAR(10)NOT NULL,
stu_sex VARCHAR(6)NOT NULL,
stu_birth DATETIME,
class VARCHAR(4)NOT NULL,
stu_email VARCHAR(40)
)
drop table student

INSERT INTO student VALUES('101','李明','女','1983-09-18','11','[e-mail]')
INSERT INTO student VALUES('102','王二','男','1984-01-01','11','[e-mail]')
INSERT INTO student VALUES('103','李开','男','1984-01-01','11','[e-mail]')
--来检查学生的邮箱地址是否相同，如果相同，输出'inserting fail'，并且回滚事务；如果不相同，则插入成功。
CREATE TRIGGER student_insert
ON student
AFTER INSERT
AS
IF exists (SELECT stu_email FROM inserted a where a.stu_email  in (select stu_email from student))
	BEGIN 
	PRINT 'Inserting fail'
	ROLLBACK 
END
ELSE
BEGIN 
	PRINT 'Inserting success'
END 

DROP TRIGGER student_insert
INSERT INTO student VALUES('114','lengbing','女','1985-12-12','11','[e-mail]')
select * from student

插入一个不同E-mail的数据，怎么事务回滚了？？？求解决方法。逻辑上没错啊

[阳泉酒家小当家]

当你在insert触发器中检查是否有这个email的时候，肯定是有的，因为当触发insert触发器的时候，这个email值已经插入到了你的student表，所以每次都会 rollback 的。 建议可以考虑，在stu_email 这个字段建一个unique索引，这样每次插入已存在的相同值的时候，就会报错的

[阳泉酒家小当家]

如果还是要用触发器的话，得修改一下触发器：

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类型; 2.这种重复性测试还是建议在前端调用写一个checkExists()，然后后台写一个Storeprocedure就不必使用TRIGGER了。

[铁歌]

3.建立EMAIL列的唯一索引很有必要。

[唐诗三百首]

create table student
(stu_ID VARCHAR(8)PRIMARY KEY,
 stu_name VARCHAR(10)NOT NULL,
 stu_sex VARCHAR(6)NOT NULL,
 stu_birth DATETIME,
 class VARCHAR(4)NOT NULL,
 stu_email VARCHAR(40))

INSERT INTO student VALUES('101','李明','女','1983-09-18','11','[e-mail]')
INSERT INTO student VALUES('102','王二','男','1984-01-01','11','[e-mail]')
INSERT INTO student VALUES('103','李开','男','1984-01-01','11','[e-mail]')


create trigger student_insert
on student for insert
as
begin
 if exists(select 1 from inserted
           where stu_email in
           (select a.stu_email 
            from student a
            left join inserted b on a.stu_ID=b.stu_ID
            where b.stu_ID is null))
 begin 
   print 'Inserting fail'
   rollback transaction 
 end
 else
 begin 
   print 'Inserting success'
 end
end


-- 插入一个不同E-mail的数据
INSERT INTO student VALUES('114','lengbing','女','1985-12-12','11','[e-mail]')

/*
Inserting success

(1 row(s) affected)
*/

select * from student

/*
stu_ID   stu_name   stu_sex stu_birth               class stu_email
-------- ---------- ------- ----------------------- ----- ----------------------------------------
101      李明         女       1983-09-18 00:00:00.000 11    [e-mail]
102      王二         男       1984-01-01 00:00:00.000 11    [e-mail]
103      李开         男       1984-01-01 00:00:00.000 11    [e-mail]
114      lengbing   女       1985-12-12 00:00:00.000 11    [e-mail]

(4 row(s) affected)
*/


-- 插入一个相同E-mail的数据
INSERT INTO student VALUES('115','lengbing','女','1985-12-12','11','[e-mail]')

/*
Inserting fail
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
*/

select * from student

/*
stu_ID   stu_name   stu_sex stu_birth               class stu_email
-------- ---------- ------- ----------------------- ----- ----------------------------------------
101      李明         女       1983-09-18 00:00:00.000 11    [e-mail]
102      王二         男       1984-01-01 00:00:00.000 11    [e-mail]
103      李开         男       1984-01-01 00:00:00.000 11    [e-mail]
114      lengbing   女       1985-12-12 00:00:00.000 11    [e-mail]

(4 row(s) affected)
*/