Oracle中用触发器实现自动记录表数据被修改的历史信息

Oracle中用触发器实现自动记录表数据被修改的历史信息。

有一些比较重要的表字段每次修改需要做历史记录，以后可以查询这个表中某些字段如何被修改过。由什么改成了什么等。

1. 我们先创建一个建议的订单表：
   
   [sql]  view plain copy

   1. CREATE TABLE "TEST"."TB_BILL" ("BILL_ID" NUMBER(10) NOT NULL,   
   2.     "BILL_NO" VARCHAR2(64) NOT NULL, "AMOUNT" NUMBER(10, 3) NOT   
   3.     NULL, "PRICE" NUMBER(10, 3) NOT NULL, "DESCRIPTION"   
   4.     VARCHAR2(1024) NOT NULL, "CREATE_DATE" DATE NOT NULL,   
   5.     CONSTRAINT "SYS_TB_BILL_PK_BILL_ID" PRIMARY KEY("BILL_ID"))    
   为了方便测试，为此表创建用于自增长的序列：
   
   [sql]  view plain copy

   1. CREATE SEQUENCE "TEST"."SQ_TB_BILL" INCREMENT BY 1 START WITH 1   
   2.     MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE   
   3.     CACHE 20 NOORDER  
   
   
2. 然后创建一个历史记录信息表，来保存历时信息：
   
   [sql]  view plain copy

   1. CREATE TABLE "TEST"."TB_BILL_HISTORY" ("HIS_ID" NUMBER(10) NOT   
   2.     NULL, "BILL_ID" NUMBER(10) NOT NULL, "CONTENT" VARCHAR2(1024)  
   3.     NOT NULL, "EVENT_TIME" DATE DEFAULT sysdate NOT NULL,   
   4.     "USER_ID" NUMBER(10) NOT NULL,   
   5.     CONSTRAINT "SYS_TB_BILL_HISTORY_PK_HIS_ID" PRIMARY   
   6.     KEY("HIS_ID"))    
   同样，为这个表创建序列：
   
   [java]  view plain copy

   1. CREATE SEQUENCE "TEST"."SQ_TB_BILL_HISTORY" INCREMENT BY 1 START WITH 1   
   2.     MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE   
   3.     CACHE 20 NOORDER  
3. 关键时刻来临，我们为TB_BILL订单表创建用于修改的触发器：
   
   [sql]  view plain copy

   1. CREATE OR REPLACE TRIGGER "TEST"."TG_TB_BILL_UPD_FIELDS_HIS"   
   2.     BEFORE  
   3. UPDATE OF "AMOUNT", "CREATE_DATE", "DESCRIPTION", "PRICE" ON "TEST"."TB_BILL" FOR EACH ROW DECLARE /*记录提单修改过的痕迹*/  
   4.   historyText TB_BILL_HISTORY.CONTENT%TYPE; /*记录日志的主要信息*/   
   5.   
   6. BEGIN  
   7.   
   8.   if :OLD.AMOUNT <> :NEW.AMOUNT then /*数量*/  
   9.     historyText:=concat(historyText,'数量：');  
   10.     historyText:=concat(historyText,replace(:OLD.AMOUNT,' ',''));  
   11.     historyText:=concat(historyText,'----->');  
   12.     historyText:=concat(historyText,replace(:NEW.AMOUNT,' ',''));  
   13.     historyText:=concat(historyText,'；');  
   14.   end if;  
   15.     
   16.   if :OLD.PRICE <> :NEW.PRICE then /*价格*/  
   17.     historyText:=concat(historyText,'价格：');  
   18.     historyText:=concat(historyText,replace(:OLD.PRICE,' ',''));  
   19.     historyText:=concat(historyText,'----->');  
   20.     historyText:=concat(historyText,replace(:NEW.PRICE,' ',''));  
   21.     historyText:=concat(historyText,'；');  
   22.   end if;  
   23.     
   24.   if (:OLD.DESCRIPTION <> :NEW.DESCRIPTION) or ((:OLD.DESCRIPTION is not null) and (:NEW.DESCRIPTION is null) ) or ((:NEW.DESCRIPTION is not null) and (:OLD.DESCRIPTION is null) ) then /*备注*/  
   25.     historyText:=concat(historyText,'备注：');  
   26.     historyText:=concat(historyText,replace(:OLD.DESCRIPTION,' ',''));  
   27.     historyText:=concat(historyText,'----->');  
   28.     historyText:=concat(historyText,replace(:NEW.DESCRIPTION,' ',''));  
   29.     historyText:=concat(historyText,'；');  
   30.   end if;  
   31.   
   32.   /*将修改后的信息放入历史记录信息表*/  
   33.   if lengthb(historyText) > 1 then  
   34.     insert into TB_BILL_HISTORY(HIS_ID,BILL_ID,CONTENT,EVENT_TIME,USER_ID) values(SQ_TB_BILL_HISTORY.nextval,:OLD.BILL_ID,historyText,sysdate,1);  
   35.   end if;  
   36. END;  
4. 接下来我们对订单表插入一条测试数据：
   
   [sql]  view plain copy

   1. insert into TB_BILL(BILL_ID,BILL_NO,AMOUNT,PRICE,DESCRIPTION,CREATE_DATE) values(SQ_TB_BILL.nextval,'No.1',1000,9.9,'Desc1',sysdate);  
   
   此时我们查询TB_BILL的数据如下：
   
   
   查询历时记录信息表的数据如下：
   
   
   
5. 然后，我们对订单表的数据进行修改，会触发上边创建的触发器：
   
   [sql]  view plain copy

   1. update TB_BILL set AMOUNT=500,PRICE=9.8,DESCRIPTION='DESC2' where BILL_ID=1  
   
   此时，查看一下TB_BILL表的数据如下：
   
   
   下面我们来看看历时记录表的信息：
   
   
   OK，非常完美，我们看到了订单的修改的历时信息；无论修改了多少次，都会以流水账的方式保存，只需要在应用中提供一个订单号即可查寻到。
   
   
   
   转载请注明出处：http://blog.youkuaiyun.com/it_wangxiangpan/article/details/8699059