if exists (select *from sysdatabases where name='TRI_school')
drop database TRI_school
create database TRI_school
on primary
(
name='TRI_schoolDB',
filename='D:\触发器\TRI_school.mdf',
size=10mb,
maxsize=100mb,
filegrowth=2mb
)
use TRI_school
if exists (select *from sysobjects where name='Student')--学生表
drop table Student
create table Student(
sno char(10)not null,
sname varchar(20)not null,
sage smallint,
ssex char(1),
sdept char(2),
xf int default 0,
jgxf int default 0,
primary key(sno),
check(ssex in ('M','F'))
)
if exists (select *from sysobjects where name='Course')--课程表
drop table Course
create table Course
(
cno char(10)not null primary key,--定义主键
cname varchar(20)not null,
cpon char(10),
credit smallint,
);
if exists (select *from sysobjects where name='SC')--选课表
drop table SC
create table SC
(
sno char(10)not null,
cno char(10)not null,
grade smallint ,
primary key(sno,cno),
check(grade>=0 and grade<=100)
)
create trigger sc_tri on sc
for update,insert,delete
as
begin
declare @ycno varchar(10),@ysno varchar(10)
declare @xcno varchar(10),@xsno varchar(10)
declare @yxf int ,@xxf int
select @ycno=cno,@ysno=sno from deleted
select @xcno=cno,@xsno=sno from inserted
if @xsno is not null--如果现在的学号存在则去学生表中查找学分是多少
begin
select @xxf=xf from student where sno=@xsno
if @xxf is null--学生表中若没有学分则置0
update student set xf=0 where sno=@xsno
end
if @ysno is not null--//如果原来这个学生就在学生表里
begin
select @yxf=credit from course where cno=@ycno--查找原学分
update student set xf=xf-@yxf where sno=@ysno--为什么要删除？
end
if @xsno is not null
begin
select @xxf=credit from course where cno=@xcno
update student set xf=xf+@xxf where sno =@xsno
end
end
--插入操作
insert into student (sno,sname,sage,ssex,sdept)
values('95001','李勇',20,'M','CS');
insert into student (sno,sname,sage,ssex,sdept)
values('95002','刘晨',19,'F','IS');
insert into student (sno,sname,sage,ssex,sdept)
values('95003','王敏',18,'F','MA');
insert into student (sno,sname,sage,ssex,sdept)
values('95004','张立',19,'M','IS');
insert into course(cno,cname,cpon,credit)
values('2','数学',null,2)
insert into course(cno,cname,cpon,credit)
values('6','数据处理',null,2)
insert into course(cno,cname,cpon,credit)
values('7','PASCAL语言','6',4)
insert into course(cno,cname,cpon,credit)
values('4','操作系统','6',3)
insert into course(cno,cname,cpon,credit)
values('5','数据结构','7',4)
insert into course(cno,cname,cpon,credit)
values('1','数据库','5',4)
insert into course(cno,cname,cpon,credit)
values('3','信息系统','1',4)
insert into sc values('95001','1',92);
insert into sc values('95001','2',85);
insert into sc values('95001','3',88);
insert into sc values('95002','2',90);
insert into sc values('95002','3',80);
insert into sc values('95003','3',80);
select *from student
select *from course
select *from sc
