关于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 条评论
登录 后参与评论

相关文章

来自专栏C/C++基础

MySQL问题集锦

(1)SELECT子句是必选的,其它子句如WHERE子句、GROUP BY子句等是可选的。

802
来自专栏乐沙弥的世界

使用 Oracle Datapump API 实现数据导出

  Oracle Datapump API 是基于PL/SQL实现的,是命令行方式下的补充。使用Datapump API可以将其逻辑备份特性将其集成到应用程序当...

1434
来自专栏Rgc

mysql数据库优化(三)--分区

分区:把一个数据表的文件和索引分散存储在不同的物理文件中。 特点:业务层透明,无需任何修改,即使从新分表,也是在mysql层进行更改(业务层代码不动)

1353
来自专栏芋道源码1024

数据库分库分表中间件 Sharding-JDBC 源码分析 —— SQL 解析(六)之删除SQL

本文主要基于 Sharding-JDBC 1.5.0 正式版 1. 概述 2. DeleteStatement 3. #parse() 3.1 #skipBet...

3177
来自专栏技术博文

多表关联是ON和WHERE的区别

很多时候,开发在书写SQL的时候不能正确的理解和运用ON和WHERE的区别。今天就简单演示介绍下(有图有真相)。 原理:数据库在通过连接多张表来返回记录时,都会...

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

【数据库】MySQL进阶一、主外键讲解

MySQL进阶主外键讲解 1.什么是外键: 主键:是唯一标识一条记录,不能有重复的,不允许为空,用来保证数据完整性 外键:是另一表的主键, 外键可...

3497
来自专栏xingoo, 一个梦想做发明家的程序员

oracle数据结构

 数据类型: 1 字符数据:CHAR VARCHAR NCHAR NVARCHAR2 LONG CLOB NCLOB  2 数字数据类型:NUMBER 唯一用来...

2076
来自专栏Android相关

SQLite---使用触发器(Trigger)

当数据库中的数据太多时,往往需要进行清理,将一些过时的数据删除,但是往往找不到合适的时机进行清理。于是SQLite提供了Trigger,当某些事件发生时,可以触...

1014
来自专栏Snova云数仓

Greenplum性能优化之路 --(一)分区表

分区表就是将一个大表在物理上分割成若干小表,并且整个过程对用户是透明的,也就是用户的所有操作仍然是作用在大表上,不需要关心数据实际上落在哪张小表里面。Green...

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

浅谈exp/imp(上) (r5笔记第81天)

作为DBA,经常需要在不同数据库环境间做数据的导入导出,exp/imp就是这样的轻便快捷的客户端工具,可以很方便的在不同数据库之间转移数据对象,即使数据库位于不...

2748

扫码关注云+社区