學習before之:new或:old轉化加工觸發器

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