oralce 触发器例子

create or replace trigger TRIG_UPDATE_WORK_GROUP_DETAIL
       --迁入时间，迁出时间，备注
  after update of SIGN_IN_TIME,SIGN_OFF_TIME,REMARK
  on OM_WORK_GROUP_DETAIL  
  for each row
declare
  -- local variables here
begin
  
   if nvl(:new.SIGN_IN_TIME,trunc(sysdate))<>nvl(:old.SIGN_IN_TIME,trunc(sysdate)) then
     insert into OM_SYS_MODIFY_LOG(id,createtime,Source_Id,Business_Code,Operation_Date,OPERATION_TYPE,FIELD_CODE,FIELD_NAME,MODIFY_PRE_VALUE,MODIFY_BACK_VALUE,
                               MODIFIER_NAME,MODIFY_OPTLOCK,MODIFY_TABLE_NAME)
                          VALUES(OM_SYS_MODIFY_LOG_SEQ.NEXTVAL,SYSDATE,:NEW.ID,:NEW.WORK_GROUP_CODE||'_'||:NEW.EMPLOYEE_NUM,SYSDATE,'UPDATE','SIGN_IN_TIME','迁入时间',to_char(:old.SIGN_IN_TIME,'yyyy-MM-dd HH24:mi:ss'),to_char(:new.SIGN_IN_TIME,'yyyy-MM-dd HH24:mi:ss'),
                          :new.LAST_UPDATOR_NAME,:new.LOCK_VERSION,'OM_WORK_GROUP_DETAIL');
    end if;
     if nvl(:new.SIGN_OFF_TIME,trunc(sysdate))<>nvl(:old.SIGN_OFF_TIME,trunc(sysdate)) then
     insert into OM_SYS_MODIFY_LOG(id,createtime,Source_Id,Business_Code,Operation_Date,OPERATION_TYPE,FIELD_CODE,FIELD_NAME,MODIFY_PRE_VALUE,MODIFY_BACK_VALUE,
                               MODIFIER_NAME,MODIFY_OPTLOCK,MODIFY_TABLE_NAME)
                          VALUES(OM_SYS_MODIFY_LOG_SEQ.NEXTVAL,SYSDATE,:NEW.ID,:NEW.WORK_GROUP_CODE||'_'||:NEW.EMPLOYEE_NUM,SYSDATE,'UPDATE','SIGN_OFF_TIME','迁出时间',to_char(:old.SIGN_OFF_TIME,'yyyy-MM-dd HH24:mi:ss'),to_char(:new.SIGN_OFF_TIME,'yyyy-MM-dd HH24:mi:ss'),
                          :new.LAST_UPDATOR_NAME,:new.LOCK_VERSION,'OM_WORK_GROUP_DETAIL');
    end if;
    if nvl(:new.REMARK,'*^$@!#%&')<>nvl(:old.REMARK,'*^$@!#%&') then
     insert into OM_SYS_MODIFY_LOG(id,createtime,Source_Id,Business_Code,Operation_Date,OPERATION_TYPE,FIELD_CODE,FIELD_NAME,MODIFY_PRE_VALUE,MODIFY_BACK_VALUE,
                               MODIFIER_NAME,MODIFY_OPTLOCK,MODIFY_TABLE_NAME)
                          VALUES(OM_SYS_MODIFY_LOG_SEQ.NEXTVAL,SYSDATE,:NEW.ID,:NEW.WORK_GROUP_CODE||'_'||:NEW.EMPLOYEE_NUM,SYSDATE,'UPDATE','REMARK','备注',:old.REMARK,:new.REMARK,
                          :new.LAST_UPDATOR_NAME,:new.LOCK_VERSION,'OM_WORK_GROUP_DETAIL');
    end if;
  
end TRIG_UPDATE_WORK_GROUP_DETAIL;