sql server 实验5.2 触发器的建立与使用

一、实验目的                                                     

理解触发器的概念和作用;了解触发器的分类及触发条件;掌握触发器的定义及应用。

二、实验原理

1、 使用CREATE TRIGGER语句定义触发器,ALTER TRIGGER语句修改触发器,DROP TRIGGER语句删除触发器。

2、 触发器分AFTER/FOR和INSTEAD OF两种类型:AFTER/FOR类型的触发器是在相应的触发语句(insert、delete、update)执行完后被触发的。如果触发语句对应的表上有完整性约束,这些完整性约束必须不违背时,相应的触发语句才能执行,然后才能触发对应的AFTER/FOR类型的触发器。INSTEAD OF类型的触发器会在触发语句(insert、delete、update)执行之前被触发,并取代相应的触发语句。

3、 在表或视图上,每个INSERT、UPDATE或DELETE语句只能创建一个INSTEAD OF类型的触发器,无法为有外键约束且指定为级联删除或级联修改的表创建DELETE或UPDATE语句上的INSTEAD OF 类型的触发器。

4、 SQL SERVER为每个触发器都创建了两个临时表INSERTED表和DELETED表,这两个表的逻辑结构与被触发器作用的表一样,用户可以读取这两个表的内容,但不能对它们进行修改,触发器执行完后,这两个表也会自动删除。当执行INSERT时,INSERTED表中保存要向表中插入的所有行;当执行DELETE时,DELETED表中保存要从表中删除的所有行;当执行UPDATE时,修改前的行保存在DELETED表中,修改后的行保存在INSERTED行中。                                                                                                                                                                                                                                                                                                            

三、实验设备

安装有SQL SERVER 2000的计算机。

四、实验示例

1、写一个允许用户一次只删除一条员工记录的触发器。

createtrigger tr_emp

onemployee for delete as

declare@row_cnt int

select@row_cnt=count(*) from deleted

if@row_cnt>1

  begin

   print '删除操作不允许删除多条人事表数据!!!'

   rollback transaction

  end

deletefrom employee

wheresex='女'         --显示'删除操作不允许删除多条人事表数据!!!'

2、创建一个触发器,只能接受女员工

createtrigger employee_insert2 on employee

afterinsert

as

ifexists(select *  from inserted wheresex='男')

  begin

       print '本公司不接受男员工!'

       rollback transaction

  end

else

  print '欢迎加入本公司!'

insertemployee values('E0021','林中正','男','管理','总经理','1974-10-01','1953-05-04',100000,'13879765667','上海市')  --显示'本公司不接受男员工!'

3、定义一触发器,保证新添加的员工的工资不能超过5000元

createtrigger employee_insert on employee

afterinsert

as

if(selectsalary from inserted)>5000

  begin

    print '新添加的员工的工资不能超过5000元!'

    rollback

  end

4、在customer表上创建一触发器,用来实现级联删除。

drop trigger del_cus; create trigger del_cus on customer instead of delete as delete sale_item where order_no in(select order_no from sales where cust_id in(select cust_id from deleted)) delete sales where cust_id in(select cust_id from deleted) delete customer where cust_id in(select cust_id from deleted)

五、实验内容

1、针对employee表写一个DELETE触发器,显示删除的员工人数。

create trigger emp_delete on employee

for delete

as

declare @num int

set @num=0

select @num=count(*) from deleted

print '删除的人数为:'+cast(@num as char(10))

delete from employee where sex='男'

drop trigger emp_delete

2、针对employee表写一个UPDATE触发器,限制每次工资额的变动不能超过原工资的20%。

create trigger update_emp on employee

after update

as

declare @new_salary float,@old_salary float

select @old_salary=salary from deleted

select @new_salary=salary from inserted

ifABS(@new_salary-@old_salary)>@old_salary*0.2

 begin

       print '每次工资额的变动不能超过原工资的20%'

      rollback transaction

end

update employee set salary=salary*0.15

3、定义一个触发器,保证新添加的员工的工资不能超过5000元

create trigger upwy_emp on employee

for insert

as

if((select salary from inserted)>5000)

begin

print '新添加的员工的工资不能超过5000元'

rollback

end

insert employee values('E0028','杨子','男','业务','职员','1980-12-1','1950-1-1',4600,'07345123596','昆明市')

insert employee values('E0029','陆毅','男','业务','职员','1980-12-2','1950-12-2',6200,'07343120496','上海市')

4、对sale_item表创建一个触发器,当插入一条销售明细记录时,如果该记录的产品数量超过5,则显示“欢迎成为本公司的VIP会员!”

create trigger ins_sait on sale_item

for insert

as

if((select qty from inserted)>5)

begin

print '欢迎成为本公司的VIP会员!'

rollback

end

insert sale_item values('1004','P0008','6','10000','1996-11-10')

5、针对sales表,定义一触发器保证参照完整性(参照customer表),不允许向sales表中插入customer表中不存在的客户。

create trigger unin_sales on sales

instead of insert

as

if((select count(*) from customer wherecust_id in(select cust_id from inserted))<0)

begin

print'不允许向sales表中插入customer表中不存在的客户'

rollback

end

insert sales values(10002,'C0556','E0013',22700.00,'1996-11-10','I000000002')

drop trigger unin_sales

6、针对employee表,定义一触发器用来保证实体完整性,阻止用户修改Employee表中的emp_no列。.

create trigger unaf_emp on employee

for update

as

declare @emp_id_i char

declare @emp_id_d char

select @emp_id_i=emp_no from inserted

select @emp_id_d=emp_no from deleted

if @emp_id_i<>@emp_id_d

begin

 print'不能修改employee表中的emp_no列'

 rollback

end

update employee

set emp_no='45645'

where emp_no='E0008'

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏java一日一条

MySQL分页性能优化指南

很多应用往往只展示最新或最热门的几条记录,但为了旧记录仍然可访问,所以就需要个分页的导航栏。然而,如何通过MySQL更好的实现分页,始终是比较令人头疼的问题。虽...

16630
来自专栏北京马哥教育

数据库基础知识:数据库中的约束和三大范式

? 一.数据库中的范式: 范式, 英文名称是 Normal Form,它是英国人 E.F.Codd(关系数据库的老祖宗)在上个世纪70年代提出关系数据库模型后...

33470
来自专栏大数据钻研

MySQL分页性能优化指南

一道面试的问题,当MySQL表中有数据量很大的时候如何做分页。。。。当时只知道在数据量很大的时候可以分表,但不知道不分表时可以怎么做。。。。唉,谁让代理商就那么...

36880
来自专栏java一日一条

MySQL分页性能优化指南

很多应用往往只展示最新或最热门的几条记录,但为了旧记录仍然可访问,所以就需要个分页的导航栏。然而,如何通过MySQL更好的实现分页,始终是比较令人头疼的问题。虽...

21430
来自专栏逸鹏说道

维护索引(1)——索引碎片

前言: DBA的日常任务并不仅仅是创建需要的索引在对应的列上,实际上,DBA还要保持索引创建的高标准。 周而复始,DBA必须盯着一些非常重要的信息: 1、 索引...

37270
来自专栏杨建荣的学习笔记

关于interval partitioning(r3笔记65天)

今天一个开发的同事问我一个问题,说在系统中目前需要一个临时的解决方案,定义了一张表,目前想设定为分区表,因为会和外围系统交互产生大量的数据,所以需要按照小时来做...

30480
来自专栏杨建荣的学习笔记

生产环境sql语句调优实战第八篇(r3笔记第24天)

生产环境中的sql语句执行时间是很关键的性能指标,如果某个sql语句执行几个小时,优化以后几分钟,几十秒的话。会有很大的成就感,同时如果某个sql语句执行10秒...

28970
来自专栏码神联盟

mysql数据库常见锁机制

关于互联网常见层次架构,由于小编还没整理完毕(预计周四推送),先来一篇数据库的干货,来满足下大家的胃口,关于mysql的行级锁、表级锁、页级锁的分析,这个在行业...

44490
来自专栏JAVA烂猪皮

用 Node + MySQL 如何处理 100G 数据

通过这个 Node.js 和 MySQL 示例项目,我们将看看如何有效地处理 数十亿行 占用 数百GB 存储空间的数据。

22250
来自专栏智能合约

数据插入失败引发的主键auto_increment问题

40630

扫码关注云+社区

领取腾讯云代金券