关于trigger过滤最大值的问题(54天)

今天碰到一个问题,开发有一个比较紧的需要,想问问我数据库这边能不能帮上忙。 如果开发那边来做,需要改代码,如果数据库这边能临时支持,代码就可以多做些测试,然后再打补丁了。 需求的情况大体是这样:有一个表的字段是number(11,4),意味着数据保持4为精度,总共长度支持11位,最大值位9999999.9999 如果超过了那个最大值(比如99999999,有8个9),想在update语句update之前能够把那个值改成9999999就可以了。 听起来好像可以使用trigger来做。简单做了个测试。 新建一个表,字段last_threshold的数据类型为number(11,4) SQL> create table test_number(last_threshold number(11,4),content varchar2(100)); Table created. 然后插入一些数据,可以看到,我插入的小数点后是5个9,也可以插入。 SQL> insert into test_number values(1.99999,''); 1 row created. SQL> insert into test_number values(9999999.9999,'a'); 1 row created. SQL> insert into test_number values(9999999.9998,'b'); 1 row created. SQL> insert into test_number values(9.999999,'c'); 1 row created. SQL> commit; Commit complete. 查看插入的数据情况,看到现实是下面的样子,有些疑惑,全都自作主张做了4舍5入了。 select *from test_number; LAST_THRESHOLD CONTENT -------------- ------------------------------ 2 10000000 a 10000000 b 10 c 设置一下精度 SQL> col last_threshold format 99999999.99999 SQL> / LAST_THRESHOLD CONTENT --------------- ------------------------------ 2.00000 9999999.99990 a 9999999.99980 b 10.00000 c SQL> col last_threshold format 9999999.9999 SQL> / LAST_THRESHOLD CONTENT -------------- ------------------------------ 2.0000 9999999.9999 a 9999999.9998 b 10.0000 c 想直接创建一个语句级的trigger,可惜失败了。 SQL> CREATE TRIGGER maxvalue_test before UPDATE of LAST_THRESHOLD ON test_number begin dbms_output.put_line(:old.last_threshold); dbms_output.put_line(:new.last_threshold); end; / 2 3 4 5 6 7 8 CREATE TRIGGER maxvalue_test * ERROR at line 1: ORA-04082: NEW or OLD references not allowed in table level triggers 重头再来,先写一个trigger来测试一下是不是好使。 SQL> CREATE TRIGGER maxvalue_test before UPDATE of LAST_THRESHOLD ON test_number for each row begin dbms_output.put_line(:old.last_threshold); dbms_output.put_line(:new.last_threshold); end; / 2 3 4 5 6 7 8 Trigger created. SQL> update test_number set last_threshold=9293.9999 where content='c'; 10 9293.9999 1 row updated. SQL> rollback; Rollback complete. SQL> select *from test_number; LAST_THRESHOLD CONTENT -------------- ------------------------------ 2.0000 9999999.9999 a 9999999.9998 b 10.0000 c 可以看到行级的触发器做了多少的处理。 验证了3条记录。 SQL> update test_number set last_threshold=9999 where content is not null; 9999999.9999 9999 9999999.9998 9999 10 9999 3 rows updated. SQL> commit; Commit complete. SQL> select *from test_number; LAST_THRESHOLD CONTENT -------------- ------------------------------ 2.0000 9999.0000 a 9999.0000 b 9999.0000 c 尝试改成最大值 SQL> update test_number set last_threshold=9999999.9999 where content is null; 2 9999999.9999 1 row updated. SQL> commit; Commit complete. SQL> select *from test_number; LAST_THRESHOLD CONTENT -------------- ------------------------------ 9999999.9999 9999.0000 a 9999.0000 b 9999.0000 c 然后开始正式的测试。 drop trigger maxvalue_test; CREATE TRIGGER maxvalue_test before UPDATE of LAST_THRESHOLD ON test_number for each row begin dbms_output.put_line('old: '||:old.last_threshold); dbms_output.put_line('new: '||:new.last_threshold); if (:new.last_threshold>9999999) then :new.last_threshold:=9999999; dbms_output.put_line('change to: '||:new.last_threshold); end if; end; / 但是测试的时候发现还是不行。 SQL> update test_number set last_threshold=99999999.9999 where content is null; update test_number set last_threshold=99999999.9999 where content is null * ERROR at line 1: ORA-01438: value larger than specified precision allowed for this column drop trigger maxvalue_test; 那改成合理范围的值呢。比如改成100. CREATE TRIGGER maxvalue_test before UPDATE of LAST_THRESHOLD ON test_number for each row begin dbms_output.put_line('old: '||:old.last_threshold); dbms_output.put_line('new: '||:new.last_threshold); if (:new.last_threshold<9999999) then :new.last_threshold:=100; dbms_output.put_line('change to: '||:new.last_threshold); end if; end; / SQL> update test_number set last_threshold=999999.9 where content is null; old: 999999.9999 new: 999999.9 change to: 100 1 row updated. SQL> commit; Commit complete. SQL> select *from test_number; LAST_THRESHOLD CONTENT -------------- ------------------------------ 100.0000 9999.0000 a 9999.0000 b 9999.0000 c 可以看到trigger的数据校验一定是在数据类型在合理范围之内。不过反过来一想也是合情合理。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-04-26

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏互联网开发者交流社区

SQL触发器实例(上)

1394
来自专栏Java帮帮-微信公众号-技术文章全总结

​【数据库】MySQL进阶三、游标简易教程

【数据库】MySQL进阶三、游标简易教程 mysql游标简易教程 从mysql V5.5开始,进行了一次大的改变,就是将InnoDB作为默认的存...

3877
来自专栏偏前端工程师的驿站

MyBatis魔法堂:Insert操作详解(返回主键、批量插入)

一、前言                                      数据库操作怎能少了INSERT操作呢?下面记录MyBatis关于INSERT...

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

生产环境大型sql语句调优实战第一篇(二) (r2笔记32天)

继续昨天的部分,上一篇的链接为: http://blog.itpub.net/23718752/viewspace-1217012/ 对这条大sql的性能瓶颈进...

2466
来自专栏数据和云

走在专家的路上,每天一条SQL优化(3)

小编寄语:本系列分享的SQL优化实例,并不一定适用于所有相似SQL或所有场景。我们只是介绍一种方法,当你再次遇到类似SQL,可以根据真实场景,选择最适合的方案。...

2527
来自专栏跟着阿笨一起玩NET

把数据库中表的内容转存为XML文件

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

生产环境sql语句调优实战第六篇(r2笔记91天)

生产环境中有大量的sql语句在运行,尽管有awr,ash做数据的收集统计,但是dba的调优工作大多数情况都是在问题已经发生后做排查的,有些sql语句可能执行的时...

2594
来自专栏数据库

mysql数据操作语句

?imageMogr2/blur/1x0/quality/75|watermark/1/image/aHR0cDovL29zNzhmNGhueS5ia3QuY2...

1805
来自专栏跟着阿笨一起玩NET

LINQ to JavaScript

JSLINQ 是一个将LINQ对象转化为JavaScript对象的工具 。它是构建在JavaScript的数组对象的基础上进行转换的,如果您使用的是一个数组,你...

512
来自专栏个人随笔

那些年我们的(具有含金量)MySQL测试题目

 请耐心阅读,下面有惊喜! 1.创建数据库 CREATE DATABASE QQDB; 2.创建各表(表结构;约束) /*******************创...

4398

扫描关注云+社区