关于trigger过滤最大值的问题

今天碰到一个问题，开发有一个比较紧的需要，想问问我数据库这边能不能帮上忙。
如果开发那边来做，需要改代码，如果数据库这边能临时支持，代码就可以多做些测试，然后再打补丁了。
需求的情况大体是这样:有一个表的字段是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的数据校验一定是在数据类型在合理范围之内。不过反过来一想也是合情合理。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23718752/viewspace-1149566/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23718752/viewspace-1149566/