一、触发器的基本用法
--触发器的创建
create trigger trigger_name --创建触发器trigger_name为触发器名称
on {table_name | view_name} --表示给表或者视图创建触发器
{for | After | Instead of } --- for 和after表示表或者视图执行完增、删、改之后才会触发,Instead of表示执行sql语句之前触发
[ insert, update,delete ] --设置监测表执行何种状态触发,例如insert 表示执行插入操作,才会触发
as
sql_statement --设置触发器需要执行的sql 语句
--触发器的删除
drop trigger trigger_name
--触发器的修改
alter trigger trigger_name
on {table_name | view_name}
{for | After | Instead of }
[ insert, update,delete ]
as
sql_statement
二、 触发器实战操作---当表执行插入操作,更新当前插入的记录
--创建触发器 检查student表是否存在插入数据
create trigger insert_student
on student
for insert
as
declare @StudentName nvarchar(50),
@ProjectName nvarchar(50),
@ClassName nvarchar(50),
@Month nvarchar(50),
@Score nvarchar(50),
@StudentID int,
@num
select @StudentID = StudentID from inserted;--取出当前学生表ID
select @Month = Month from inserted; --取出当前插入数据的月份数据
select @StudentName = StudentName from inserted; --取出当前插入数据的学生姓名
select @ProjectName = ProjectName from inserted; --取出当前插入数据的课程名称
select @ClassName =ClassName from inserted; --取出当前插入数据的班级名称
select @num=COUNT(*)
from student
where Month = convert(varchar(7), DATEADD(MM,-1,@Month+'-01'), 121)
and StudentName = @StudentName and ProjectName = @ProjectName
and ClassName = @ClassName
if(@num='1')
begin
select @Score = Score
from student
where Month = convert(varchar(7), DATEADD(MM,-1,@Month+'-01'), 121)
and StudentName= @StudentName and ProjectName = @ProjectName and ClassName = @ClassName
end;
update student set Score = @Score
where StudentID = @StudentID