数据库实验之触发器

首先申明，本蒟蒻实力微弱，所以下面的操作仅供参考。

虽然文章中代码没有查询全表的语句，但我的建议是select语句可以写几条，反正也不难，不是吗？

1.  在表Student中建立删除触发器，实现表Student和表SC的级联删除，也就是只要删除表Student中的元组学号为s1，则表SC中SNO为s1的元组也要删除；建立完触发器后删除Student中学号为‘00030’的元组，看看表SC中SNO为‘00030’的选课记录是否也一起删除；

go
create trigger s_delete
on student
instead of delete
as
begin
delete from sc
where sno in(select sno from deleted)
delete from student
where sno in(select sno from deleted)
end

delete from Student
where sno='00030'

1.未执行触发器时：

2.执行触发器后，触发器所在位置

3.sc表和student表删除前后：

2.  在表Course中增加一个职业规划选修课，为(005,职业规划,4,0014)，在表SC中建立一个触发器，实现规定年龄24岁以上（包括24岁）的学生才能选修职业规划这门课程，如果年龄小于24岁，则输出’年龄小于24，不能选修该门课程’，插入失败，用SQL语句在SC表中分别插入(‘00001’,’005’,null)和(‘00005’,’005’,null)看看结果,试试使用一条SQL语句插入(‘00012’,’005’,null)和(‘00013’,’005’,null)看看结果，如果两行数据都插入不成功说明原因，并通过修改触发器实现一条SQL语句插入(‘00012’,’005’,null)和 (‘00013’,’005’,null)时，(‘00013’,’005’,null)数据插入成功；

insert into course values('005','职业规划',4,'0014');

go
create trigger s_insert2
on sc
instead of insert
as
begin
declare @sno2 char(5)
select @sno2 = sno from inserted
if((select cno from inserted)='005' and (select sage from Student where sno = @sno2)<24)
print'年龄小于24，不能选修该门课程'
else
print '成功！'
insert into sc
select * from inserted
where @sno2 in(select sno from inserted)
end

insert into sc values('00001','005',null)
insert into sc values('00005','005',null)

insert into sc values('00012','005',null)
insert into sc values('00013','005',null)

1.添加一门新课程：

2.运行之后

3.  在表SC中建立更改触发器，实现表SC中的修改后的成绩不能低于修改前的成绩，如果修改后的成绩低于修改前的成绩，则输出’修改后的成绩比修改前低，不能修改’，修改失败，用update语句把学号为00001，课程号为001的成绩分别改为90和70，看看结果,试试用update语句把课程号为001的成绩都改为70，看看结果，要求触发器能够实现001号课程低于70分的成绩都能修改成为70，高于70分的成绩都不修改；

go
create trigger s_update
on sc
after update
as
begin
if(update(score))
begin
declare @score1 numeric(3,1),@score2 numeric(3,1)
select @score1=sc.score from sc -- 修改前
select @score2=score from deleted -- 修改后
if(@score1>@score2)
print '成功！'
end
else
update sc
set sc.Score=@score2 from sc,deleted
where sc.SNo=deleted.SNo and sc.CNo=deleted.CNo
print'修改后的成绩比修改前低，不能修改'
end
-- 有bug，无论输入什么数字，都会更新，而且都会报'修改后的成绩比修改前低，不能修改'

update sc
set score = 90 where sno='00001' and cno = '001'
update sc
set score = 70 where sno='00001' and cno = '001'
update sc
set score = 70 where cno='001' and score < 70

这个问题有个bug，目前我没有很好的解决方法，希望有大佬路过，也可以指点一下。

1.修改之前：

2.执行触发器后，执行语句后打印：

、

3.修改之后：

4.  在表Teacher中创建触发器，实现如果更新了表Teacher中的年龄和工资，则输出’更新了年龄和工资’，如果更新了年龄没有更新工资，则输出’更新了年龄’，如果更新了工资而没有更新年龄，则输出’更新了工资’，创建完后使用SQL语句把tno为001的年龄加1，把tno为002的工资加1，把tno为003的年龄和工资都加1，看看结果；

go
create trigger t_update
on teacher
after update
as
begin
declare @age int,@sal float
select @age = age from deleted
select @sal = sal from deleted
-- 是否就是说inserted存储的是操作之前的，而deleted存储的是操作之后的？？？
if(@age!=(select age from inserted) and @sal!=(select sal from inserted))
print '更新了年龄和工资'
else if(@age!=(select age from inserted) and @sal=(select sal from inserted))
print '更新了年龄'
else if(@age=(select age from inserted) and @sal!=(select sal from inserted))
print '更新了工资'
end

update Teacher
set age = age+1 where tno='0001'
update Teacher
set sal = sal + 1 where tno='0002'
update Teacher
set age = age + 1,sal = sal+1  where tno='0003'

1.修改之前：

2.执行触发器后，执行语句后打印：

3.修改之后：

5.  在不删除触发器的前提下，使题3创建的触发器无效；

-- 禁用触发器（让触发器不起作用）：
-- ALTER TABLE 表名 DISABLE TRIGGER 触发器名
alter table sc disable trigger s_update

6.  创建一个名为tri_Delete_C的触发器，要求首先判断数据库中是否已经存在名为tri_Delete_C的触发器，如果存在，首先删除，再创建，触发器要求删除一门课程时候，首先判断该课程有否有人选，如果有人选，则不能删除,并通过测试数据验证该触发器的执行情况；

if(exists(select * from sysobjects where xtype='tr' and name='tri_Delete_C'))
begin
drop trigger tri_Delete_C
print '已删除'
end
go
create trigger tri_Delete_C
on course
instead of delete
as
begin
if(exists(select * from sc inner join deleted on sc.CNo=deleted.CNo))
print '有人选，不能删除!'
else
declare @cno char(5)
select @cno=cno from deleted
print '没人选，已删除！'
delete from course
where cno in(select cno from deleted)
end

delete from course where cno='005'

insert into course values('006','体育',3,'0010')
delete from course where cno='006'

1.查看是否有名为tri_Delete_C的触发器：

注：其实没有，不过用来修改我们创建的tri_Delete_C触发器挺方便的，想改就先删后改：

2.判断课程是否有人选：

A.有

B.没有

7.  在表Teacher中创建safty触发器，拒绝用户对数据库中的Teacher表进行删除和更改操作,创建完后通过修改Teacher表的结构和删除Teacher表来测试一下效果（提示：DDL触发器，关键字为DROP_TABLE , ALTER_TABLE）。

go
create trigger safty
on database
for DROP_TABLE,ALTER_TABLE
as
begin
print '拒绝用户对数据库中的Teacher表进行删除和更改操作'
rollback transaction
end

delete from teacher where tno='0002' -- ok,not succeed
drop table teacher					 -- no
alter table teacher add Email varchar(50);--succeed

--DDL触发器标准语法  
-- CREATE [ OR ALTER ] TRIGGER trigger_name   
-- ON { ALL SERVER | DATABASE }   --触发器应用范围为当前数据库|服务器
-- [ WITH <ddl_trigger_option> [ ,...n ] ]  
-- { FOR | AFTER } --指定仅在触发SQL语句中指定的所有操作成功启动后才触发触发器
-- { event_type | event_group } [ ,...n ]  
-- AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  --触发条件和操作，在尝试操作时，在Transact-SQL语句中指定的触发操作将生效。

对拒绝用户的删除和修改行为，采取的是“回滚”方式，妙！