实例15(触发器).sql

经典的sql资料

--上课内容：第七章 存储过程及触发器 (触发器)


 -- 7.2.1 触发器的概念：

    -- 触发器是一种特殊的存储过程

    -- 触发器定义在特定的表或视图上，在表中数据发生变化时自动强制执行

    -- 触发器可以用于SQL Server约束、默认值和规则的完整性检查


   -- 特点：

    -- 1. 触发器可以强制比用 CHECK 约束定义的约束更为复杂的约束

    -- 2. 触发器也可以评估数据修改前后的表状态，并根据其差异采取对策

    -- 3. 一个表中的多个同类触发器（INSERT、UPDATE 或 DELETE）允许采取多个不同的对策以响应同一个修改语句


   -- 功能：

    -- 1. 通过触发器的使用，可以强制实现不同表中的逻辑相关数据的引用完整性、一致性

    -- 2. 触发器自动执行，确保对数据记录的更改要符合在触发器里定义的规则



   -- 创建触发器原则：

    -- 1. 只能在当前数据库中创建触发器。

    -- 2. 创建触发器的权限默认给数据库的所有者，但是不可以把权限授权其他用户创建触发器。

    -- 3. 触发器是数据库对象，其名称必须遵守标识符命名规则。

    -- 4. 不能在视图上创建触发器


   -- 分类：

    -- 1. AFTER 触发器：

      -- 在触发语句完成之后执行

      -- 可以为触发的操作（INSERT UPDATE DELETE）定义多个AFTER 触发器


    -- 2. INSTEAD OF 触发器：

      -- SQL SERVER 2000 新增的功能

      -- 这种类型触发器在数据变动之前被触发，并取代变动数据的操作，而去执行触发器定义的操作

      -- 只能为触发的操作（INSERT UPDATE DELETE）定义一个INSTEAD OF 触发器


   -- 触发器与普通存储过程的区别：

    -- 1. 存储过程可以直接调用， 而触发器不能被直接调用，是通过事件被触发而执行

    -- 2. 存储过程接受并可以传递参数， 而触发器不可以传递参数


   -- 触发器与 CHECK 约束的区别：

    -- 1. 触发器可以强制执行更加复杂的数据完整性

    -- 2. 触发器可以引用其他表中的列


  -- deleted 表和 inserted 表

    -- SQL SERVER 2000自动创建和管理这些表

    -- 存在于内存中，不能直接对表中的数据进行修改，（记录的是你修改记录的信息）

    -- 用于找到数据修改前后的差异，并可以基于这个差异采取行动

    -- 触发器表：

    -- deleted 表: 存储DELETE、 UPDATE 语句所影响的行的复本，

    -- inserted 表: 存储INSERT、 UPDATE 语句所影响的行，新数据行被同时添加到inserted 表和触发器表

 -- 7.2.2 创建触发器

  -- 7.2.2.1 创建INSERT触发器
create trigger insert_data on exam after insert as 
if(select t_grade from inserted)>100
begin
print 'not over 100'
end

-- 执行插入语句，因为设置了外键，stu_info表里没有'20040306'，
-- 所以出错，提示INSERT 语句与 COLUMN FOREIGN KEY 约束 'con_t' 冲突
insert into exam values('20040306','100101',101)

-- 所以，先要在stu_info表里插入stu_id='20040306'的记录
insert into stu_info values('20040306','王静','女','1984-12-12')

-- 再执行插入语句
insert into exam values('20040306','100101',101)

select * from exam
-- 我们发现这条错误的记录还是插入了


 -- 修改触发器
alter trigger insert_data on exam after insert as 
if(select t_grade from inserted)>100
begin
print '分数不能大于100'
rollback
end

-- 我们再插入刚才的记录，就提示“分数不能大于100”
insert into exam values('20040306','100101',101)


  -- 7.2.2.2 创建UPDATE触发器
create trigger update_data on exam after update as
if update(t_grade) 
begin
  if(select t_grade from inserted)>100
    begin
    print '分数不能大于 100'
    rollback transaction
    end
end

update exam set t_grade=195 where t_number='20040306' and c_number='100101'

select * from exam

  -- 7.2.2.3 创建INSERT、UPDATE触发器
create trigger update_data on exam for insert,update as
if update(t_grade) 
begin
  if(select t_grade from inserted)>100
    begin
    print '分数不能大于 100'
    rollback transaction
    end
end

insert into exam values('20040306','100101',101)
select * from exam
update exam set t_grade=101 where t_number='20040301' and c_number='100101'
select * from exam



  -- 7.2.2.4 创建INSTEAD OF触发器
create trigger not_delete on exam instead of delete as print'not delete'

-- 删除一条记录
delete from exam where t_number='20040306' and c_number='100101'


 -- 7.2.3 修改触发器
ALTER TRIGGER 触发器名 
ON 表名
 [WITH ENCRYPTION]
AS 触发器内容

-- 例：
alter trigger insert_data on exam with encryption after insert as 
if(select t_grade from inserted)>100
begin
print 'not over 100'
end

 -- 7.2.4 查看触发器

  -- 查看触发器信息
sp_help insert_data		

  -- 查看触发器定义文本
sp_helptext insert_data


  -- WITH ENCRYPTION 参数

  -- 注意：WITH ENCRYPTION 和视图的一样，在修改时没写就是默认可以让他人看到你存储过程的定义

  -- 查看触发器相关性
sp_depends insert_data

 -- 7.2.5 更改触发器名称

sp_rename 旧触发器名，新触发器名 
sp_rename insert_data，insert_data_new


 -- 7.2.6 删除触发器
DROP TRIGGER 触发器名
drop trigger insert_data_new

 -- 7.2.7 如何判断要创建的触发器是否存在

IF EXIST(SELECT name FROM sysobjects WHERE name='inse_data' AND type='TR')
DROP TRIGGER inse_data
go
create trigger inse_data on 表名 after insert as 内容

   -- 同样，对存储过程创建之前也会做一个判断

IF EXIST(SELECT name FROM sysobjects WHERE name='pro_data' AND type='P')
DROP PROCEDURE pro_data
go
create procedure pro_data  as 内容



-- 准备工作：
create table stu_info
(
t_number char(8),
t_name char(10),
t_gender char(2),
t_birthday datetime
)

create table course
(
c_number char(6),
c_name char(20),
c_credit int,
c_hour int,
c_teacher char(10)
)


create table exam
(
t_number char(8),
c_number char(6),
t_grade decimal(5,2)
)

--向stu_info表插入数据
insert into stu_info values('20040301','张华','女','19840113')
insert into stu_info values('20040302','王立','男','19830624')
insert into stu_info values('20040303','蒋超','男','19841115')
insert into stu_info values('20040304','王浩雨','男','19851020')
insert into stu_info values('20040305','张静','女','19840418')
insert into stu_info values('20050301','李华','女','19830113')
insert into stu_info values('20050302','张立','男','19840624')
insert into stu_info values('20050303','黄超','男','19851125')
insert into stu_info values('20050304','汪雨','男','19861020')
insert into stu_info values('20050305','王静','女','19850418')

--向course表插入数据
insert into course values('100101','高等数学',2,60,'赵金')
insert into course values('100102','大学英语',3,80,'王维')
insert into course values('100103','大学物理',2,60,'李华')
insert into course values('100104','大学英语',4,80,'刘杰')
insert into course values('100105','大学英语',NULL,80,'刘杰')

--向exam表插入数据
insert into exam values('20040301','100101',79)
insert into exam values('20040301','100102',88)
insert into exam values('20040302','100101',90)
insert into exam values('20040302','100103',75)
insert into exam values('20040303','100101',79)
insert into exam values('20040303','100102',75)
insert into exam values('20040303','100103',95)
insert into exam values('20040304','100102',43)
insert into exam values('20040304','100103',68)
insert into exam values('20040305','100101',64)
insert into exam values('20040305','100102',87)
insert into exam values('20040305','100103',92)


-- 练习：

-- 1. 在test数据库的course表上创建一个INSERT触发器inse_c_data,
   -- 如果插入的记录的c_hour字段值小于50,则提示'学时不能小于50',不做插入操作，并验证


-- 2. 查看inse_c_data的定义信息


-- 3. 修改触发器inse_c_data,加密定义文本，并验证


-- 4. 查看触发器inse_c_data的信息


-- 5. 查看触发器inse_c_data的相关性


-- 6. 重命名触发器inse_c_data为inse_course_date


-- 7. 删除触发器inse_course_data


-- 8. 在test数据库的course表上创建一个UPDATE触发器update_c_data,
   -- 如果插入的记录的c_credit字段值大于10,则提示'学分不能大于10',不做插入操作，并验证


-- 9. 在test数据库的stu_info表上创建一个INSERT、UPDATE触发器tri_gender，使输入的性别只能为'男' 或 '女'，并验证


-- 10. 在test数据库的course表上创建INSTEAD OF触发器not_dele_c,使不能删除course表的记录


-- 11. 在test数据库里，创建视图，查询所有exam表的记录


-- 12. 通过视图v_exam 删除学生学号为20040301的记录






-- 练习参考答案：

-- 1. 在test数据库的course表上创建一个INSERT触发器inse_c_data,
   -- 如果插入的记录的c_hour字段值小于50,则提示'学时不能小于50',不做插入操作，并验证
create trigger inse_c_data on course after insert as 
if(select c_hour from inserted)<50
begin
print '学时不能小于50'
rollback
end

insert into course values('100107','概率论',4,40,'宋佳')
select * from course
insert into course values('100107','概率论',4,60,'宋佳')

-- 2. 查看inse_c_data的定义信息
sp_helptext inse_c_data

-- 3. 修改触发器inse_c_data,加密定义文本，并验证
alter trigger inse_c_data on course with encryption after insert as 
if(select c_hour from inserted)<50
begin
print '学时不能小于50'
rollback
end

sp_helptext inse_c_data

-- 4. 查看触发器inse_c_data的信息
sp_help inse_c_data

-- 5. 查看触发器inse_c_data的相关性
sp_depends inse_c_data

-- 6. 重命名触发器inse_c_data为inse_course_date
sp_rename inse_c_data,inse_course_data

-- 7. 删除触发器inse_course_data
drop trigger inse_course_data

-- 8. 在test数据库的course表上创建一个UPDATE触发器update_c_data,
   -- 如果插入的记录的c_credit字段值大于10,则提示'学分不能大于10',不做插入操作，并验证

create trigger update_c_data on course after update as
if update(c_credit) 
begin
  if(select c_credit from inserted)>10
    begin
    print '学分不能大于 10'
    rollback transaction
    end
end

update course set c_credit=19 where  c_number='100101'


-- 9. 在test数据库的stu_info表上创建一个INSERT、UPDATE触发器tri_gender，使输入的性别只能为'男' 或 '女'，并验证
create trigger tri_gender on stu_info
for insert,update as
if update(t_gender)
begin
  if(select t_gender from inserted) not in('男','女')
  print '性别错误'
  rollback
end

-- 插入记录
insert into stu_info values('20040307','王静','王','1984-12-12')
-- 提示“性别错误”，不进行插入操作
select * from stu_info
-- 修改记录
update stu_info set t_gender='wn' where t_number='20040301'
select * from stu_info

-- 10. 在test数据库的course表上创建INSTEAD OF触发器not_dele_c,使不能删除course表的记录

create trigger not_dele_c on course instead of delete as print'不能删除course表的记录'

-- 删除一条记录
delete from course where c_number='100101'

-- 11. 在test数据库里，创建视图，查询所有exam表的记录

   CREATE VIEW V_E AS SELECT S.T_NUMBER,S.T_NAME,E.T_GRADE FROM STU_INFO AS S,EXAM AS E WHERE S.T_NUMBER=E.T_NUMBER

-- 12. 通过视图v_exam 删除学生学号为20040301的记录
select * from v_e
delete from v_e where t_number='20040301'
create trigger tri_dele on v_e instead of delete as
begin
delete from exam where t_number='20040301'
end










create table aa(aid int primary key,aname,adate datetime default getdate())