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
begin
select * from deleted
select * from inserted
select '增长速度>250%不能修改'
rollback
end
else
select '修改成功'
select * from zg
update zg set 英语=英语*3 where 姓名='杨杰'
--通过触发器调用其他函数
create proc p1 as
select * from zg
create trigger u4 on zg for insert,delete,update as
exec p1
insert into zg(姓名,学号,班级)values('小王',20100638,'小语')
--多个触发器运行顺序
create trigger i5 on zg for insert,update as
select '你在插入数据'
create trigger u5 on zg for update,insert as
select '你在修改数据'
update zg set 姓名='张军' where 姓名='张晓军'
--触发器
create trigger j1 on kc for update,delete,insert
as
select( '你正在修改数据');
select * from deleted
delete from kc where 课程名称='计算机导论'
insert into kc (学号,课程名称,课程代码,学分) values('1010','计算机导论','D032T',2) update kc set 学号=1007 where 学号=1006
create trigger j2 on kc for delete
as
declare @count varchar(20)
select @count=str(@@rowcount)+'个学生被删除'
select @count
return
delete from kc where 学分=1.5
create trigger j3 on jun for insert,update
as
if(select sum(年龄) from jun )>400
begin
select '你们年龄大入要求：不予录取'
rollback
end
else
begin
select '恭喜你，你被录取了'
end
insert into jun (学号,爱好,年龄) values('111','编程',20)
update zg set 总成绩=语文+数学+英语
select * from zg
create trigger y2 on zg for insert,update
as
if( select sum(总成绩) from zg )>2000
begin
select ' 你通过了考试，欢迎你晋级'
rollback
end
else
select '很抱歉，你没有通过考试'
select * from zg
update zg set 语文=语文-50 select 语文from zg。