数据库-触发器设计

0.假设数据库中存在以下表

--课程信息表

CREATE TABLE Course(
Cno Char(4)  PRIMARY KEY,
Cname Char(20) NOT NULL,
Cpno Char(4),
Ctype Char(8) NULL,
Chour Smallint NULL,
Ccredit Decimal NULL,
Cterm Smallint NULL,
Cabstract Varchar(200) NULL,
SelectNum smallint;
FOREIGN KEY(Cpno) REFERENCES Course(Cno)
); 

--学生选课表

CREATE TABLE SC(
Sno Char(11) NOT NULL, 
Cno char(4) NOT NULL,
Grade decimal(5,1) NULL,
Primary Key (Sno,Cno),
Foreign Key(Sno) References Student(Sno),
Foreign Key(Cno) References Course(Cno)
);

课程基本信息表中存在SelectNum字段，该字段的作用是，统计有多少个学生选修了此门课程。

因此，我们需要统计 select count(*) from SC , Course where  SC.Cno = Course.Cno; 把它写入到SelectNum中

SelectNum记录了学生选修Course表中该们课程的总人数

当学生选课表中需要添加一条记录时，相应的选课表的的SelectNum字段要跟着发生改变SelectNum = SelectNum+1，类似于此，我们为了操作某个表或某个字段而促使其他表或字段发生改变，我们可以使用触发器来做。

1.什么是触发器

触发器是在对表进行插入，修改删除操作时自动执行的存储过程，通常作用是用于强制业务规则，可看做是一种高级的约束！

2.触发器的特点:

2.1 触发器定义在特定的表上，与表相关

2.2自动触发执行

2.3不能调用执行

2.4是一个事务 （可回滚）

3.为什么要使用触发器

保证数据的一致性

4.触发器的工作机制

触发器触发时，系统自动在内存中创建只读的inserted表或deleted表，不允许用户对其做任何修改；触发器执行完城后这两个表自动删除。（临时表）

5.T-SQL实现触发器

5.1创建一个触发器

create trigger trigger_name

on table_name

[with emcryption]  --可选，表示加密sql文本

  {for | after | instead of} {delete | insert | update}

as

T-SQL 语句

5.2禁用或启用触发器

alter table table_name {enable | disable } trigger trigger_name

5.3删除触发器

drop trigger trigger_name

6.触发器示例

--如果系统表中存在name='AddSelectCourse' and type='tr'名称为AddSelectCourse
--的触发器，则删除该触发器
IF EXISTS(
SELECT name FROM sysobjects 
WHERE name = 'AddSelectCourse' AND type='tr')
drop trigger AddSelectCourse


--创建一个触发器的，名称为AddSelectCourse的触发器
--如果插入的数据在SC表中已经存在，打印出‘该课程你只能选修一次，你已经选修了该门课程’
--回滚该事务
create trigger AddSelectCourse on SC for insert as
if(select COUNT(*) from inserted,SC where SC.Sno = inserted.Sno 
and SC.Cno = inserted.Cno)>1 
begin 
print '该课程你只能够选修一次，你已经选修了该门课程'
rollback transaction
end
else
--如果Course表内的SlectNum为null，则把该null字段改为0
begin
declare @Cno varchar(4)
select  @Cno = Cno from inserted
if(select SelectNum from Course where Cno = @Cno) is null 
begin
update Course set SelectNum = 0 where Cno = @Cno
end
--修改Course表中字段SelectNum的值
update Course 
set SelectNum = SelectNum + 1
where Cno = @Cno
print'选修课程'+@Cno+'的人数已经增加!'
end
go
--测试
select * from Course;
select * from SC;
insert into SC(Sno , Cno)
values ('20121323001','C001')


insert into SC(Sno , Cno)
values ('20121323001','C001')


--创建一个触发器，不允许删除Teacher表中的任何数据
create trigger NoDelTeacher
on Teacher
for delete
as 
 if(select COUNT(*) from deleted)>0
begin
print '你不能删除Teacher表中的任何数据！'
rollback transaction
end


select * from Teacher
insert into Teacher values('20061208','李军','男','教师','1983-05-21','1998-08-30','SE');
delete from Teacher where Tname = '李军';