Oracle中利用Trigger进行工作

Oracle数据库中,有些情况下,对数据记录需要记录日志,或保存操作历史等情况.在此我们可以借助“数据库Trigger”进行。下面以一例进行说明：

 

CREATE OR REPLACE TRIGGER aits_auth_group_auth_trga_diu
  AFTER update OR DELETE OR  INSERT on  aits_authority_group_auth
  for each row
/*****************************************************************************************
*
* Trigger Name           : aits_auth_group_auth_trga_diu
* Description            : Log insert, Modify ,delete action to History table
* Version                : 1.0

*****************************************************************************************/

 

 

 

declare

BEGIN

   IF inserting THEN
       INSERT INTO aits_auth_group_auth_history
         (application_code, auth_id, ranage_code,
          group_code, module_code, function_code,
          creation_date, created_by, last_update_date,
          last_updated_by, action_type)
       VALUES
         (:new.application_code, :new.auth_id, :new.ranage_code,
          :new.group_code, :new.module_code, :new.function_code,
          :new.creation_date, :new.created_by, :new.last_update_date,
          :new.last_updated_by, aits_pak_common.InsertDesc);
   ELSIF DELETING THEN
       INSERT INTO aits_auth_group_auth_history
         (application_code, auth_id, ranage_code,
          group_code, module_code, function_code,
          creation_date, created_by, last_update_date,
          last_updated_by, action_type)
       VALUES
         (:OLD.application_code, :OLD.auth_id, :OLD.ranage_code,
          :OLD.group_code, :OLD.module_code, :OLD.function_code,
          :OLD.creation_date, :OLD.created_by, :OLD.last_update_date,
          :OLD.last_updated_by, aits_pak_common.DeleteDesc);
   ELSIF UPDATING  THEN
       IF UPDATING('application_code') OR UPDATING('auth_id') OR UPDATING('ranage_code')
          OR UPDATING('group_code') OR UPDATING('module_code') OR UPDATING('function_code')   THEN
             INSERT INTO aits_auth_group_auth_history
               (application_code, auth_id, ranage_code,
                group_code, module_code, function_code,
                creation_date, created_by, last_update_date,
                last_updated_by, action_type)
             VALUES
               (:new.application_code, :new.auth_id, :new.ranage_code,
                :new.group_code, :new.module_code, :new.function_code,
                :new.creation_date, :new.created_by, :new.last_update_date,
                :new.last_updated_by, aits_pak_common.UpdateDesc);
       END IF;
  END IF;
end aits_auth_group_auth_trga_diu;