随手敲的,没有测试。楼主自己测试吧
create trigger t_update_stu
on Student
for update
as
if update(claid)
begin
if exists(select 1 from class c,department d,inserted s ,deleted t where c.DepID =d.DepID and t.StuID=s.StuID and
s.claid=c.claid and t.claid=c.claid)
begin
update class
set ClaCount=clacount+1
from class,inserted
where class.claid=inserted.claid
update class
set ClaCount=clacount+1
from class,deleted
where class.claid=deleted.claid
end
else
begin
update department
set ClaCount=clacount+1
from class,inserted,department
where class.deptid=department.deptid and class.claid=inserted.claid
update department
set ClaCount=clacount=-1
from class,inserted,department
where class.deptid=department.deptid and class.claid=inserted.claid
end
end
create table Department
(
DepID varchar(10) primary key,--系部编号
DepName varchar(10),--系部名称
DepHead varchar(10),--系主要任
DepCount int--系部人数
)
go
insert into Department(DepID,DepName,DepHead,DepCount)values('1001','软件系','王国军',3000)
insert into Department(DepID,DepName,DepHead,DepCount)values('1002','网络技术系','张强',2000)
insert into Department(DepID,DepName,DepHead,DepCount)values('1003','数字艺术系','万强华',1000)
go--班级表
create table Class
(
ClaID varchar(10) primary key,--班级编号
ClaName varchar(10),--班级姓名
ClaHead varchar(10),--辅导员
ClaCount int,--班级人数
DepID varchar(10) foreign key references Department(DepID)--所在系部
)
go
insert into Class(ClaID,ClaName,ClaHead,ClaCount,DepID)values('2001','0800161','王军1',50,'1002')
insert into Class(ClaID,ClaName,ClaHead,ClaCount,DepID)values('2002','0800162','王军2',45,'1002')
insert into Class(ClaID,ClaName,ClaHead,ClaCount,DepID)values('2003','0800163','王力3',55,'1003')
go--学生表
create table Student
(
StuID int primary key,--学生学号
StuName varchar(10),--学生姓名
StuBir datetime,--学生生日
StuAdd varchar(50),--学生地址
ClaID varchar(10) references Class(ClaID)--所在班级
)
go
insert into Student(StuID,StuName,StuBir,StuAdd,ClaID)values(1,'王国军1','1989/7/24','重庆市1','2001')
insert into Student(StuID,StuName,StuBir,StuAdd,ClaID)values(2,'王国军2','1989/7/24','重庆市1','2001')
go
create trigger updateclass
on student
for update
as
update class set ClaCount=ClaCount+ isnull((select top 1 clacount from(
select claid,sum(clacount)as clacount from(
select claid,count(*)as clacount from inserted group by claid
union all
select claid,-count(*)as clacount from deleted group by claid
)t group by claid having sum(clacount)<>0
)t1 where claid=t2.claid),0) from class t2
go
update student set claid=2002 where stuid<=2
select * from class
drop trigger updateclass
drop table student,class,Department
/*
ClaID ClaName ClaHead ClaCount DepID
---------- ---------- ---------- ----------- ----------
2001 0800161 王军1 48 1002
2002 0800162 王军2 47 1002
2003 0800163 王力3 55 1003
*/
以上触发器适宜于批量更新.
只做了student表的,class表的触发器与之类似.
UPDATE SET
insert into 这些语言是最基本的
create trigger TriggerInsertStu
on Student
for insert
as
begin
declare @ClaID varchar(10)
declare @DepID varchar(10)
select @ClaID=ClaID from inserted
select @DepID=DepID from Class where ClaID=@ClaID
update Class set ClaCount+=1 where ClaID=@ClaID
update Department set DepCount+=1 where DepID=@DepID
end
-------这是我们的语法格式,能不能按照这种格式写更新学生的触发器啊?
-----不能这样写的话,你们给个注释好吗?我是初学者啊!
比如update class set ClaCount=ClaCount+ isnull((select top 1 clacount from(
select claid,sum(clacount)as clacount from(
select claid,count(*)as clacount from inserted group by claid
union all
select claid,-count(*)as clacount from deleted group by claid
)t group by claid having sum(clacount)<>0
)t1 where claid=t2.claid),0) from class t2你拆为update class set ClaCount=ClaCount+ isnull(check_expression,replacement_value ) group by claid having sum(clacount)<>0
里面嵌套的是check_expression 是将被检查是否为 NULL的表达式,replacement_value是check_expression 为 NULL时将返回的表达式.ISNULL里面最里层:select claid,count(*)as clacount from inserted group by claid
union all
select claid,-count(*)as clacount from deleted group by claid 容易懂 select top 1 clacount from(
select claid,sum(clacount)as clacount from 最里层得到的值 t不知道我说明白没???
你们能不能说明语法的意思啊?书上也没有。给个解释啊!
使用指定的替换值替换 NULL。语法
ISNULL ( check_expression , replacement_value ) 参数
check_expression将被检查是否为 NULL的表达式。check_expression 可以是任何类型的。replacement_value在 check_expression 为 NULL时将返回的表达式。replacement_value 必须与 check_expresssion 具有相同的类型。Sum
返回在某一集合上对数值表达式求得的和。<>0就是不等于0group by是分组
你能说说下面这每句话的意思吗?,遇到关键字都解释下,好不?谢谢你啊,你是好人啊!create trigger updateclass
on student
for update
as
update class set ClaCount=ClaCount+ isnull((select top 1 clacount from(
select claid,sum(clacount)as clacount from(
select claid,count(*)as clacount from inserted group by claid
union all
select claid,-count(*)as clacount from deleted group by claid
)t group by claid having sum(clacount)<>0
)t1 where claid=t2.claid),0) from class t2
go
update student set claid=2002 where stuid<=2
select * from class
drop trigger updateclass
drop table student,class,Department
指定组或聚合的搜索条件。HAVING 通常与 GROUP BY 子句一起使用。如果不使用 GROUP BY 子句,HAVING 的行为与 WHERE 子句一样。
Union
返回对两个集合进行 union 运算所生成的集合,可以保留重复的成员。语法
Union(«Set1», «Set2»[, ALL])替代语法 1
{«Set1», «Set2»}替代语法 2
«Set1» + «Set 2»注释
此函数返回 «Set1» 和 «Set2» 的 union 运算结果,并在默认情况下消除重复项。ALL 标志表示在并集中保留重复项。从尾部删除重复项。 也可以将逗号分隔的集合列表用括号括起来或使用 + 运算符,通过 union 算法合并集合。其他的就没讲的了
select top 1 clacount from 表 取表的第一行记录
create table Department
(
DepID varchar(10) primary key,--系部编号
DepName varchar(10),--系部名称
DepHead varchar(10),--系主要任
DepCount int--系部人数
)
go
insert into Department(DepID,DepName,DepHead,DepCount)values('1001','软件系','王国军',3000)
insert into Department(DepID,DepName,DepHead,DepCount)values('1002','网络技术系','张强',2000)
insert into Department(DepID,DepName,DepHead,DepCount)values('1003','数字艺术系','万强华',1000)
go--班级表
create table Class
(
ClaID varchar(10) primary key,--班级编号
ClaName varchar(10),--班级姓名
ClaHead varchar(10),--辅导员
ClaCount int,--班级人数
DepID varchar(10) foreign key references Department(DepID)--所在系部
)
go
insert into Class(ClaID,ClaName,ClaHead,ClaCount,DepID)values('2001','0800161','王军1',50,'1002')
insert into Class(ClaID,ClaName,ClaHead,ClaCount,DepID)values('2002','0800162','王军2',45,'1002')
insert into Class(ClaID,ClaName,ClaHead,ClaCount,DepID)values('2003','0800163','王力3',55,'1003')
go--学生表
create table Student
(
StuID int primary key,--学生学号
StuName varchar(10),--学生姓名
StuBir datetime,--学生生日
StuAdd varchar(50),--学生地址
ClaID varchar(10) references Class(ClaID)--所在班级
)
go
insert into Student(StuID,StuName,StuBir,StuAdd,ClaID)values(1,'王国军1','1989/7/24','重庆市1','2001')
insert into Student(StuID,StuName,StuBir,StuAdd,ClaID)values(2,'王国军2','1989/7/24','重庆市1','2001')
go
create trigger updateclass
on student
for update
as
update class set ClaCount=ClaCount+ isnull((select top 1 clacount from( --将统计人数更新到class表
select claid,sum(clacount)as clacount from( --转出转入人数对班级合计
select claid,count(*)as clacount from inserted group by claid --统计各班级有几人转入
union all
select claid,-count(*)as clacount from deleted group by claid --统计各班级有几人转出
)t group by claid having sum(clacount)<>0
)t1 where claid=t2.claid),0) from class t2
go
create trigger updatedep
on class
for update
as
update department set depCount=depCount+ isnull((select top 1 depcount from( --将统计人数更新到department表
select depid,sum(depcount)as depcount from( --转出转入人数对系合计
select depid,sum(clacount)as depcount from inserted group by depid --统计各系有转入班级的人数
union all
select depid,-sum(clacount)as depcount from deleted group by depid --统计各系有转出班级的人数
)t group by depid having sum(depcount)<>0
)t1 where depid=t2.depid),0) from department t2
go
--情况1:不改变班级
update student set stuadd='重庆市2' where stuname like '王国%'
select * from class
/*
ClaID ClaName ClaHead ClaCount DepID
---------- ---------- ---------- ----------- ----------
2001 0800161 王军1 50 1002
2002 0800162 王军2 45 1002
2003 0800163 王力3 55 1003
*/
--情况2:改变班级不改变系
update student set claid=2002 where stuname like '王国%'
select * from class
/*
ClaID ClaName ClaHead ClaCount DepID
---------- ---------- ---------- ----------- ----------
2001 0800161 王军1 48 1002
2002 0800162 王军2 47 1002
2003 0800163 王力3 55 1003
*/
--情况3:改变班级及系
update student set claid=2003 where stuname like '王国%'
select * from class
select * from department
/*
ClaID ClaName ClaHead ClaCount DepID
---------- ---------- ---------- ----------- ----------
2001 0800161 王军1 48 1002
2002 0800162 王军2 45 1002
2003 0800163 王力3 57 1003DepID DepName DepHead DepCount
---------- ---------- ---------- -----------
1001 软件系 王国军 3000
1002 网络技术系 张强 1998
1003 数字艺术系 万强华 1002
*/
go
drop trigger updateclass
drop table student,class,Department
select claid,count(*)as clacount from inserted group by claid --统计各班级有几人转入
select claid,-count(*)as clacount from deleted group by claid --统计各班级有几人转出select ......
union all
select ...... --两查询结果合并select claid,sum(clacount)as clacount from( --转出转入人数对班级合计
--前面的查询结果
)t group by claid having sum(clacount)<>0 --以班级编号分组,统计转入(为正)转出(为负)人数update class set ClaCount=ClaCount+ isnull((select top 1 clacount from( --将统计人数更新到class表
--前面合计的数目
))t1 where claid=t2.claid),0) from class t2 --利用班级号为标记进行更新以上为触发器内容基于班级的触发器对系科表进行更新,语句类似.