create trigger T_insert on 选课表 after insert as begin update a set STU_NUM=isnull(STU_NUM,0)+1 from 课程表 a join inserted i on a.课程号=i.课程号 end
if object_id('学生表') is not null drop table [学生表] go create table [学生表]([学号] varchar(10),[姓名] varchar(10),[年龄] int,[性别] nvarchar(1)) insert into [学生表] select '2008001','张三',20,'男' union all select '2008002','李四',21,'女' union all select '2008003','王五',20,'男' if object_id('课程表') is not null drop table [课程表] go create table [课程表]([课程号] varchar(10),[课程名] varchar(10),[学分] int,[选修人数] int) insert into [课程表] select 'A001','数学',1000,0 union all select 'A002','语文',1000,0 union all select 'A003','英语',1000,0if object_id('选课表') is not null drop table [选课表] go create table [选课表]([学号] varchar(10),[课程号] varchar(10),[成绩] int)create trigger [tri_选课表] on [选课表] for insert as update [课程表] set [选修人数]=[选修人数]+(select count(*) from inserted i where i.[课程号]=k.[课程号]) from [课程表] k insert into [选课表] select '2008001','A001',100 union all select '2008001','A002',120 union all select '2008002','A001',90 union all select '2008001','A003',80 select * from [选课表]
after insert
as
begin
update a
set STU_NUM=isnull(STU_NUM,0)+1
from
课程表 a
join
inserted i on a.课程号=i.课程号
end
if object_id('学生表') is not null
drop table [学生表]
go
create table [学生表]([学号] varchar(10),[姓名] varchar(10),[年龄] int,[性别] nvarchar(1))
insert into [学生表]
select '2008001','张三',20,'男' union all
select '2008002','李四',21,'女' union all
select '2008003','王五',20,'男'
if object_id('课程表') is not null
drop table [课程表]
go
create table [课程表]([课程号] varchar(10),[课程名] varchar(10),[学分] int,[选修人数] int)
insert into [课程表]
select 'A001','数学',1000,0 union all
select 'A002','语文',1000,0 union all
select 'A003','英语',1000,0if object_id('选课表') is not null
drop table [选课表]
go
create table [选课表]([学号] varchar(10),[课程号] varchar(10),[成绩] int)create trigger [tri_选课表] on [选课表] for insert
as
update [课程表] set
[选修人数]=[选修人数]+(select count(*) from inserted i where i.[课程号]=k.[课程号])
from [课程表] k insert into [选课表]
select '2008001','A001',100 union all
select '2008001','A002',120 union all
select '2008002','A001',90 union all
select '2008001','A003',80 select * from [选课表]
select * from [课程表]