SQL触发器实例

--建立触发器，显示修改人数

create trigger d1 on jun for insert,update as

select '你正在修改数据'

declare @a varchar(20)

select @a=str(@@rowcount)+'个学生被修改'

select @a

select * from jun

return

insert into jun(爱好) values('跑步')

update jun set 爱好='打球' where 学号<='103'

--建立触发器，当男生人数在6个以内可以加入，否则不能加入

create trigger i1 on zg for insert as

if((select count(性别) from zg where 性别='男')>6)

select '不能插入，男生人数已满。'

else

select'插入成功'

select * from zg

insert into zg(学号,姓名,性别) values(20100636,'江渝','男')

--建立触发器，如果语文成绩在200以内可以修改，否则不能修改

create trigger u1 on zg for update as

if((select max(语文) from zg )>200)

begin

select '你不能修改'

rollback

end

else

select '修改成功'

select * from zg

update zg set 语文=语文+10 where 姓名='唐荣强'

update zg set 语文=语文-20 where 姓名='张军'

update zg set 语文=语文+30 where 姓名='张军'

--建立触发器，显示删除和修改的内容

create trigger d2 on zg for insert,update,delete as

select * from deleted

select * from inserted

select * from zg

delete from zg where 班级=Null

delete from zg where 姓名='张军'

--建立触发器，如果zg表中没有此人的姓名，则不能在kc表中进行修改

create trigger i2 on kc for insert,update as

if(not exists(select 姓名from zg where 姓名=(select 姓名from inserted))) begin

select '不能修改数据'

rollback

select * from kc

end

else

begin

select '修改成功'

select * from kc

end

update kc set 选修人数=选修人数+100 where 姓名='张军'

--建立触发器，更行zg时也更新kc

create trigger u2 on zg for update as

select * from deleted

select * from inserted

update kc set 姓名=(select min(姓名) from inserted )where 姓名=(select min(姓名) from deleted)

select * from zg

select * from kc

update zg set 姓名='张晓军' where 姓名='张军'

--建立触发器，如果加入的数>2000就不能插入并将默认值为500

create trigger i3 on zg for insert as

if((select 语文from inserted)>2000)

begin

select '语文>2000不合适'

update zg set 语文=500

select * from zg

rollback

end

else

select '修改成功'

insert into zg(班级,姓名,学号,语文) values('小数','王五',20100637,2500)

--建立触发器，不能删除总成绩最高的人

create trigger d3 on zg for delete as

if( select 总成绩from deleted) >= ( select max(总成绩)from zg)

begin

select '删除的数为：'

select * from deleted

select '不能删除成绩最高的一位'

rollback

end

else

select '删除成功'

select * from zg

delete from zg where 姓名='唐荣强'

--建立触发器，英语增长不超过250%

create trigger u3 on zg for update as

if(select max(英语) from inserted )/(select min(英语) from deleted)>2.5