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 rowdeclare  -- local variables herebegin  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;

 

oracle添加日誌表