SQL> create or replace trigger tri_trans
  2  before insert on test_trim  --before型，因为要在插入表前进行数据转换
  3  for each row
  4  declare
  5  msg varchar2(100):='it exceeds the constraint';
  6  begin
  7  :new.a:=trunc(:new.a);--在真正要插入列前，对要插入的值先取整数转化，然后把这个转化后的整数插入到表test_trim
  8  :new.b:=upper(:new.b);
  9  if :new.a=88 or :new.b='MV' then --用if :new且then诱发raise_application_error(oracle错误号，错误提示信息）
 10  raise_application_error(-20000,msg);
 11  end if;
 12  dbms_output.put_line('before translate trigger on test_trim');--触发器每次打印的内容，这里是行级触发器哟
 13  end;
 14  /

Trigger created.

SQL> select * from test_trim;

no rows selected

SQL> desc test_trim;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER(3,1)
 B                                                  VARCHAR2(20)

SQL> insert into test_trim values(88,'mv');--触发了以上错误，故不插入数据到表中
insert into test_trim values(88,'mv')
            *
ERROR at line 1:
ORA-20000: it exceeds the constraint  --这不就是触发器中定义的错误提示信息吗
ORA-06512: at "SCOTT.TRI_TRANS", line 7
ORA-04088: error during execution of trigger 'SCOTT.TRI_TRANS'

SQL> insert into test_trim values(88,'MV');--同上
insert into test_trim values(88,'MV')
            *
ERROR at line 1:
ORA-20000: it exceeds the constraint
ORA-06512: at "SCOTT.TRI_TRANS", line 7
ORA-04088: error during execution of trigger 'SCOTT.TRI_TRANS'

SQL> insert into test_trim values(888,'MV');
insert into test_trim values(888,'MV')
                             *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL> insert into test_trim values(99,'MV');
insert into test_trim values(99,'MV')
            *
ERROR at line 1:
ORA-20000: it exceeds the constraint
ORA-06512: at "SCOTT.TRI_TRANS", line 7
ORA-04088: error during execution of trigger 'SCOTT.TRI_TRANS'

SQL> insert into test_trim values(99,'MV1');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_trim;

         A B
---------- --------------------
        99 MV1

SQL> insert into test_trim values(98.99,'haha');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_trim;

         A B
---------- --------------------
        99 MV1
        99 HAHA

SQL> set serveroutput on
SQL> insert into test_trim values(98.99,'haha2');--仅取a列99(因为第一先执行：number列数据四舍五入，然后才是before触发器trunc转化，最后是真正的数据插入操作(分了三个子步哟）
before translate trigger on test_trim ---这是触发器定义的打印内容

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_trim;

         A B
---------- --------------------
        99 MV1
        99 HAHA
        99 HAHA2

SQL> insert into test_trim values(98.89,'mvp');---只取a列98,且对b列转化为大写，最后一并插入数据到表中
before translate trigger on test_trim

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_trim;--查处数据，进行核对

         A B
---------- --------------------
        99 MV1
        99 HAHA
        99 HAHA2
        98 MVP