触发器(1)
create trigger grade_update /*为表sc1创建名为grade_update的触发器*/
on sc1
for update,insert
as
update sc1 set grade =100
where grade<100
alter trigger grade_update /*修改grade_update触发器*/
on sc1
for update,insert
as
declare @sname char (10) ,@sname1 char(10)
select @sname=sc1.sno ,@sname1=student1.sname from sc1,student1 where sc1.sno=student1.sno and grade > 100
update sc1 set grade =90
print '学号: '+@sname+' 姓名:'+@sname1+ ' 成绩超过100分!!!! ' /*修改grade_update触发器*/
update sc1 set grade=200 /*看效果*/
where sno='95001'
/*触发器实例1*/
create table tmp_sc /*建立临时表tmp_sc*/
( sname char(8),
ssex char(2),
)
create trigger gra_update /*建立触发器gra_update*/
on student1
for delete
as
declare @sname char (10)
declare @ssex char(10)
select @sname=sname, @ssex=ssex from deleted
insert into tmp_sc values(@sname,@ssex)
print @sname+'和'+@ssex+'had insert into tmp_student'
delete from student1 where sname='刘文' /*看效果*/
/*触发器实例2*/
create trigger gra_update
on student1
for delete
as
declare @sname char(10)
select @sname=sno from deleted
if @sname='98124'
begin
rollback transaction
print '98124同学不可以删除!'
end
else
begin
print @sname+'同学已经删除!'
end