《数据库高级应用技术04-(触发器).ppt》由会员分享，可在线阅读，更多相关《数据库高级应用技术04-(触发器).ppt（41页珍藏版）》请在三一办公上搜索。
1、1,高等职业技术院校教材,数据库高级应用技术 主编：温立辉,2,触发器,本单元教学目标理解触发器的概念认识、了解触发器的作用理解触发器的原理、过程掌握触发器的开发语法,3,触发器概念,触发器是一种特殊的存储过程在插入、删除、修改特定表中数据时触发执行拥有比数据库本身更强大的数据控制能力,4,触发器作用,安全性可以基于数据库的值使用户具有操作数据库的某种权利#可以基于时间限制用户的操作例如不允许下班后和节假日修改数据库数据#可以基于数据库中的数据限制用户的操作例如不允许股票的价格的升幅一次超过10%数据审计可以跟踪用户对数据库的操作#审计用户操作数据库的语句#把用户对数据库的更新写入审计表,5,
2、触发器作用,数据约束#实现数据完整性检查和约束例如回退任何企图买进超过自己资金的货物#提供可变的缺省值连环更新#修改或删除时级联修改或删除其它表中的与之匹配的行#修改或删除时把其它表中与之匹配的行设成NULL值#修改或删除时把其它表中与之匹配的行级联设成缺省值,6,触发器原理,使用临时表使用临时表备份之前的数据逐行检查对每一行数据逐一进行检查以上两点大大增加了系统开销一般不能随意使用触发器,7,创建触发器,创建语法CREATE TRIGGER+触发器名称+触发时间点+触发事件+ON+表名+FOR EACH ROWBEGINEND触发时间点：BEFORE或AFTER指明是在触发事件之前还是之后执
3、行触发事件：INSERT、UPDATE、DELETE事件如：CREATE TRIGGER upd_check BEFORE UPDATE ON account FOR EACH ROWBEGINEND,8,删除触发器,删除语法DROP TRIGGER+触发器名称如：DROP TRIGGER upd_check,9,触发器案例-建表,-创建表格：tab1DROP TABLE IF EXISTS tab1;CREATE TABLE tab1(tab1_id varchar(11);-创建表格：tab2DROP TABLE IF EXISTS tab2;CREATE TABLE tab2(tab2_
4、id varchar(11);,10,第一个触发器,-功能：往tab1表添加记录后自动将此记录增加到tab2表中DELIMITER/DROP TRIGGER IF EXISTS t_afterinsert_on_tab1;CREATE TRIGGER t_afterinsert_on_tab1 AFTER INSERT ON tab1 FOR EACH ROW BEGIN insert into tab2(tab2_id)values(new.tab1_id);END;/DELIMITER;,11,触发器测试,往tab1插入记录INSERT INTO tab1(tab1_id)values(0
5、001)检索tab1、tab2数据SELECT*FROM tab1SELECT*FROM tab2,12,课程练习1,创建触发器:t_afterdelete_on_tab1功能作用：删除tab1表记录后自动将tab2表中对应的记录删去,13,参考代码,14,new与old,old：代表操作执行前的数据行new：代表操作执行后的数据行若要执行如下语句：update User set score=80 where user_id=1则old表示未执行update语句前user_id=1这行记录则new表示执行update语句后user_id=1这行记录old.字段名=未执行操作前的该行对应的某字段
6、值new.字段名=执行操作后的该行对应的某字段值old.score=60new.score=80,15,new与old,new赋值：只能在before中使用，不能在after中使用更新操作前使用before先赋值，再插入到数据库中如，以下语句是正确的：CREATE TRIGGER updateprice BEFORE insert ON consumeinfo FOR EACH ROW BEGIN set new.金额=0;END;在after中：new赋值已经结束，只能读取内容如，以下语句是错误的：CEATE TRIGGER updateprice AFTER insert ON consu
7、meinfo FOR EACH ROW BEGIN set new.金额=0;END;,16,new与old,总结归纳：new可在before触发器中赋值、取值可在after触发器中取值old只能用于取值，因为赋值没意义INSERT语句，只有new合法DELETE语句，只有old才合法UPDATE语句，可以同时使用new和old,17,课程练习2,创建触发器:upd_check 功能作用：修改Account表中的记录的amount字段时，作如下检查：如果修改后的值大于100，则值为100如果修改后的值小于0，则值为0如果修改后的值为旧值2倍以上，则值为旧值2倍,18,建表脚本,CREATE D
8、ATABASE IF NOT EXISTS mydb;USE mydb;DROP TABLE IF EXISTS account;CREATE TABLE account(id int(10)unsigned NOT NULL auto_increment,user varchar(45)NOT NULL,amount int(11)NOT NULL default 0,PRIMARY KEY(id)ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;INSERT INTO account(id,user,amount)VALUES(1,李知
9、龙,55),(2,卢中明,80),(3,乔大平,30);,19,参考代码,20,After与before,after和before的区别：after是先完成业务数据的增删改，再触发触发的语句晚于监视的业务语句无法影响前面的增删改动作也就是说先执行业务操作，再做触发操作before是先完成触发操作，再执行业务数据的增删改触发的语句先于监视的业务语句我们就有机会影响即将发生的操作,21,异常抛出,MySQL现有版本中不支持自定义异常当某处需要抛出异常时，可抛出一个系统异常(类似运行异常)如：故意往不存在的表中插入数据等抛出异常时，正在执行的本次所有操作会终止执行并回滚所有数据,22,异常抛出场景,
10、新进来的数据不符合业务逻辑，如：仓库最大商品库存数为10，订单要求一次性购买数量20银行帐户上只有50元，想要支付100元帐单权限不足，不允许操作，如：普通用户通过非法途径操作核心资源表在非工作日修改业务数据,23,触发器异常案例-建表,-创建表格：mytabDROP TABLE IF EXISTS mytab;CREATE TABLE mytab(tab_id varchar(11);,24,触发器-异常案例,-功能：往 mytab 表添加记录时，触发器中将抛出异常，导致所有操作终止，并回滚所有操作的数据DELIMITER/DROP TRIGGER IF EXISTS t_exception
11、_trigger;CREATE TRIGGER t_exception_trigger AFTER INSERT ON mytab FOR EACH ROW BEGIN-tab3不存在，将自动抛出异常 insert into tab3(tab_id)values(new.tab_id);END;/DELIMITER;,25,测试,往mytab插入记录INSERT INTO mytab(tab_id)values(id_01);检索mytab数据SELECT*FROM mytab;,26,课程练习3,创建触发器:pay_check 功能作用：当储户从帐户上扣款支付时检查其余额是否足以支付如果余额
12、不足则拒绝此次支付交易如果余额充足则允许完成此次支付交易存储过程proc_pay模拟支付过程(即:扣款直接调用此存储过程)proc_pay(IN user_account int,IN pay_money int)第一个参数为用户的扣款帐号，对应Account表的id字段第二个参数为用户的扣款金额，对应Account表的amount字段建表与存储过程脚本已给出,27,数据库环境脚本,CREATE DATABASE IF NOT EXISTS mydb;USE mydb;DROP TABLE IF EXISTS account;CREATE TABLE account(id int(10)uns
13、igned NOT NULL auto_increment,user varchar(45)NOT NULL,amount int(11)NOT NULL default 0,PRIMARY KEY(id)ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;INSERT INTO account(id,user,amount)VALUES(1,李知龙,55),(2,卢中明,80),(3,乔大平,30);DELIMITER/CREATE PROCEDURE proc_pay(IN user_account int,IN pay_money in
14、t)BEGIN update account set amount=(amount-pay_money)where id=user_account;END;/DELIMITER;,28,数据审计,审计目的：为了追溯数据修改的合法性、合理性一般由两张数据表构成一张是业务表普通用户可以拥有此表读写权限一张是审计表普通用户不拥有此表读写权限超级用户才拥有此表读写权限,29,数据审计案例,交警部门信息系统车辆收费表：存储车辆收费信息的表t_car用户可以对该表的数据作update操作需要审计对该表上数据的记录值修改信息，以备查询、跟踪使用触发器记录所有用户对表t_car，进行UPDATE操作修改数据的
15、行为进行记录,30,车辆收费信息表结构,DROP TABLE IF EXISTS t_car;CREATE TABLE t_car(id int(10)unsigned NOT NULL auto_increment,car_number varchar(45)default NULL COMMENT 车牌号,card_number varchar(45)default NULL COMMENT 银行卡号,pay decimal(6,1)default NULL COMMENT 金额,PRIMARY KEY(id)ENGINE=InnoDB DEFAULT CHARSET=utf8;,31,审
16、计内容,审计表存储的内容：修改前pay字段的值 修改后pay字段的值 记录被修改的时间 登陆数据库服务器修改数据的ip地址登陆数据库服务器的帐号信息,32,审计表结构,DROP TABLE IF EXISTS t_record;CREATE TABLE t_record(id int(10)unsigned NOT NULL auto_increment,username varchar(45)default NULL COMMENT 登录mysql的用户名,client_ip varchar(45)default NULL COMMENT 远程访问mysql服务器的客户端ip地址,updat
17、e_Before_pay varchar(45)default NULL COMMENT 修改前的金额,update_After_pay varchar(45)default NULL COMMENT 修改后的金额,gmt_create timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 创建时间,PRIMARY KEY(id)ENGINE=InnoDB DEFAULT CHARSET=utf8;,33,触发器代码,DELIMITER/DROP TRIGGER IF EXISTS tr
18、i_t_car;CREATE TRIGGER tri_t_car BEFORE UPDATE ON t_car FOR EACH ROW BEGIN IF NEW.payOLD.pay THEN INSERT INTO t_record(username,client_ip,update_Before_pay,update_After_pay)VALUES(substring(user(),1,(instr(user(),)-1),substring(user(),(instr(user(),)+1),old.pay,new.pay);END IF;END/DELIMITER;,34,数据初始
19、化脚本,INSERT INTO t_car(car_number,card_number,pay)VALUES(SUBSTRING(RAND(),3,20),SUBSTRING(RAND(),3,10),SUBSTRING(RAND(),3,3),(SUBSTRING(RAND(),3,20),SUBSTRING(RAND(),3,10),SUBSTRING(RAND(),3,3),(SUBSTRING(RAND(),3,20),SUBSTRING(RAND(),3,10),SUBSTRING(RAND(),3,3),(SUBSTRING(RAND(),3,20),SUBSTRING(RAND
20、(),3,10),SUBSTRING(RAND(),3,3),(SUBSTRING(RAND(),3,20),SUBSTRING(RAND(),3,10),SUBSTRING(RAND(),3,3),(SUBSTRING(RAND(),3,20),SUBSTRING(RAND(),3,10),SUBSTRING(RAND(),3,3),(SUBSTRING(RAND(),3,20),SUBSTRING(RAND(),3,10),SUBSTRING(RAND(),3,3),(SUBSTRING(RAND(),3,20),SUBSTRING(RAND(),3,10),SUBSTRING(RAND(
21、),3,3),(SUBSTRING(RAND(),3,20),SUBSTRING(RAND(),3,10),SUBSTRING(RAND(),3,3),(SUBSTRING(RAND(),3,20),SUBSTRING(RAND(),3,10),SUBSTRING(RAND(),3,3),(SUBSTRING(RAND(),3,20),SUBSTRING(RAND(),3,10),SUBSTRING(RAND(),3,3),(SUBSTRING(RAND(),3,20),SUBSTRING(RAND(),3,10),SUBSTRING(RAND(),3,3),(SUBSTRING(RAND()
22、,3,20),SUBSTRING(RAND(),3,10),SUBSTRING(RAND(),3,3),(SUBSTRING(RAND(),3,20),SUBSTRING(RAND(),3,10),SUBSTRING(RAND(),3,3),(SUBSTRING(RAND(),3,20),SUBSTRING(RAND(),3,10),SUBSTRING(RAND(),3,3);,35,审计测试,本地修改t_car表的pay字段值：UPDATE t_car SET pay=100.5 WHERE ID=1;远程修改t_car表的pay字段值每一学生远程连接上教师数据库服务器修改t_car表的pa
23、y字段值查看审计表t_record的数据,36,课堂练习4,写一个触发器，实现对用户的权限审计功能即当用户为：root，登录IP为：localhost 才能对业务表(order_detail)进行增、删、改操作判断条件：user()=rootlocalhost,37,数据库环境脚本,DROP TABLE IF EXISTS order_detail;CREATE TABLE order_detail(order_id int(10)unsigned NOT NULL auto_increment,commodity varchar(45)NOT NULL,commodity_num int(1
24、0)unsigned NOT NULL,order_money float NOT NULL,order_time datetime NOT NULL,commodity_type varchar(45)default NULL,PRIMARY KEY(order_id)ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;INSERT INTO order_detail(order_id,commodity,commodity_num,order_money,order_time,commodity_type)VALUES(1,衬衣,9,9
25、00,2015-07-02 12:13:20,服装),(2,帽子,30,600,2015-08-07 12:13:40,服装),(3,裤子,8,640,2015-08-17 11:13:20,服装),(4,大米,100,350,2015-07-20 12:13:20,食品),(5,零食,50,200,2015-08-11 12:13:40,食品),(6,蔬菜,30,90,2015-07-09 12:12:30,食品),(7,面包,20,80,2015-08-25 12:13:40,食品),(8,风车,10,70,2015-07-21 12:13:30,玩具),(9,汽球,40,60,2015-
26、08-13 12:13:45,玩具),(10,钢笔,25,500,2015-07-10 12:13:35,文具),(11,铅笔,150,300,2015-08-14 12:13:45,文具),(12,毛笔,10,50,2015-07-03 11:13:42,文具),(13,作业本,200,400,2015-08-18 12:13:15,文具),(14,文件夹,50,150,2015-08-17 12:10:45,文具);DROP TABLE IF EXISTS user;CREATE TABLE user(id int(10)unsigned NOT NULL auto_increment,d
27、b_user varchar(45)NOT NULL,db_ip varchar(45)NOT NULL,PRIMARY KEY(id)ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;,38,单元作业1,有商品(g)和订单(o)两张表：假设卖出某种商品若干数量，我们需要做两件事1.往订单表插入一条对应记录2.更新商品表中此种商品的剩余数量（原数量-对应的销售数量）写一个触发器实现自动级联两张表功能，即：当往订单表(o)插入某种商品的销售记录时，自动更新商品表(g)的此种商品剩余数量（原数量-对应的销售数量）,39,数据库环境脚本,-商品
28、表DROP TABLE IF EXISTS g;CREATE TABLE g(id int(10)unsigned NOT NULL auto_increment,name varchar(45)default NULL,num int(11)default NULL,PRIMARY KEY(id)ENGINE=InnoDB DEFAULT CHARSET=utf8;-订单表DROP TABLE IF EXISTS o;CREATE TABLE o(oid int(10)unsigned NOT NULL auto_increment,gid varchar(45)NOT NULL,much
29、int(11)NOT NULL,PRIMARY KEY(oid)ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into g(name,num)values(墨水,10),(钢笔,10),(练习本,10);,40,单元作业2,续单元作业1写一个触发器实现如下功能：1.当客户撤销一个订单的时候在订单表(o)直接删除一个订单，同时自动更新对应商品表(g)的商品数量（原数量+对应的撤单数量）2.当客户修改一个订单的数量时自动更新对应的商品表(g)的商品数量（原数量+/-对应的变化数量）,41,单元作业3,续单元作业1写一个触发器实现如下功能：1.当客户下订单的时候如果订单数量超过商品表(g)的商品库存数量，则直接拒绝此次交易2.当客户修改订单的数量时如果订单增加的数量超过商品表(g)的商品库存数量，直接拒绝此次交易如果订单的数量小于0，直接拒绝此次交易,
