oracle添加日志表

--创建表

CREATE TABLE KNET_DOMAIN_DNS_FORWARDED 
(
  ID VARCHAR2(36 BYTE) DEFAULT sys_guid() NOT NULL 
, KEYWORD VARCHAR2(256 BYTE) NOT NULL 
, CNAME VARCHAR2(1000 BYTE) 
, URL VARCHAR2(1000 BYTE) NOT NULL 
, CREATE_DATE DATE DEFAULT sysdate NOT NULL 
, UPDATE_DATE DATE 
, AUDIT_STATUS VARCHAR2(16 BYTE) 
, AUDIT_DATE DATE 
, AUDIT_REASON VARCHAR2(512 BYTE) 
, IS_FORWARDED VARCHAR2(20 BYTE) DEFAULT 'N' 
, CONSTRAINT KNET_DOMAIN_DNS_FORWARDED_PK PRIMARY KEY 
  (
    ID 
  )
 )

CREATE TABLE KNET_DOMAIN_DNS_FORWARDED_LOG 
(
  LOG_ID VARCHAR2(36 BYTE) NOT NULL 
, LOG_TIME DATE NOT NULL 
, LOG_OP VARCHAR2(128 BYTE) NOT NULL 
, ID VARCHAR2(36 BYTE) NOT NULL 
, KEYWORD VARCHAR2(256 BYTE) NOT NULL 
, CNAME VARCHAR2(1000 BYTE) 
, URL VARCHAR2(1000 BYTE) NOT NULL 
, CREATE_DATE DATE NOT NULL 
, UPDATE_DATE DATE 
, AUDIT_STATUS VARCHAR2(16 BYTE) 
, AUDIT_DATE DATE 
, AUDIT_REASON VARCHAR2(512 BYTE) 
, IS_FORWARDED VARCHAR2(20 BYTE) 
, CONSTRAINT KNET_DOMAIN_DNS_FORWARDED__PK PRIMARY KEY 
  (
    LOG_ID 
  )
)

--创建序列  
 CREATE SEQUENCE  
   KNET_DOMAIN_DNS_FORWARDED_SEQ
   MINVALUE 1 
   MAXVALUE 999999999999999 
   INCREMENT BY 1 
   START WITH 1 CACHE 20 NOORDER  NOCYCLE ;

--创建触发器
create or replace TRIGGER DOMAIN_DNS_FORWARDED_TIGER
  after insert or update or delete on KNET_DOMAIN_DNS_FORWARDED
  for each row
declare
  -- local variables here
begin
  if inserting then
   INSERT INTO KNET_DOMAIN_DNS_FORWARDED_LOG (LOG_ID,LOG_TIME,LOG_OP,ID,KEYWORD,CNAME,URL,CREATE_DATE,UPDATE_DATE,AUDIT_STATUS,AUDIT_DATE,AUDIT_REASON,IS_FORWARDED)
   VALUES(trim(to_char(KNET_DOMAIN_DNS_FORWARDED_SEQ.nextval, '000000000000009')),sysdate,'1',:new.ID,:new.KEYWORD,:new.CNAME,:new.URL,:new.CREATE_DATE,:new.UPDATE_DATE,:new.AUDIT_STATUS,:new.AUDIT_DATE,:new.AUDIT_REASON,:new.IS_FORWARDED);
  elsif updating then
   INSERT INTO KNET_DOMAIN_DNS_FORWARDED_LOG (LOG_ID,LOG_TIME,LOG_OP,ID,KEYWORD,CNAME,URL,CREATE_DATE,UPDATE_DATE,AUDIT_STATUS,AUDIT_DATE,AUDIT_REASON,IS_FORWARDED)
   VALUES(trim(to_char(KNET_DOMAIN_DNS_FORWARDED_SEQ.nextval, '000000000000009')),sysdate,'2',:new.ID,:new.KEYWORD,:new.CNAME,:new.URL,:new.CREATE_DATE,:new.UPDATE_DATE,:new.AUDIT_STATUS,:new.AUDIT_DATE,:new.AUDIT_REASON,:new.IS_FORWARDED);
  else
  INSERT INTO KNET_DOMAIN_DNS_FORWARDED_LOG (LOG_ID,LOG_TIME,LOG_OP,ID,KEYWORD,CNAME,URL,CREATE_DATE,UPDATE_DATE,AUDIT_STATUS,AUDIT_DATE,AUDIT_REASON,IS_FORWARDED)
   VALUES(trim(to_char(KNET_DOMAIN_DNS_FORWARDED_SEQ.nextval, '000000000000009')),sysdate,'0',:old.ID,:old.KEYWORD,:old.CNAME,:old.URL,:old.CREATE_DATE,:old.UPDATE_DATE,:old.AUDIT_STATUS,:old.AUDIT_DATE,:old.AUDIT_REASON,:old.IS_FORWARDED);
  end if;
end DOMAIN_DNS_FORWARDED_TIGER;