数据库触发器（建表-创建触发器）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
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