Oracle:复合触发器


----- CF_DEPTUCORGANIZATION  INSERT UPDATE DELETE 触发器
CREATE  OR REPLACE TRIGGER tr_del_CF_DEPTUCORGANIZATION
BEFORE INSERT OR UPDATE OR DELETE ON CF_DEPTUCORGANIZATION
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
BEGIN
    IF INSERTING THEN
    
   INSERT INTO SYS_APP_LOG(ID , LOG_TYPE, MDF_TABLE_NAME , OPERATE_TYPE ,BEFORE_RECORD , AFTER_RECORD , MENDER_NAME , CREATED_BY,CREATION_DATE )
       VALUES(SYS_GUID(), 'DB_TRIGGER', 'CF_DEPTUCORGANIZATION' ,'INSERT', '','DEPTID='||CAST( :NEW.DEPTID AS VARCHAR2(1000))||',ORGANIZATIONID='||CAST(:NEW.ORGANIZATIONID AS VARCHAR2(1000))||',CREATEUSERID='||CAST ( :NEW.CREATEUSERID AS VARCHAR2(4000))||',CREATEDATE='||CAST ( :NEW.CREATEDATE AS VARCHAR2(4000))  , '', :NEW.MODIFYUSERID,sysdate );
       
        ELSIF UPDATING then
    
          
   INSERT INTO SYS_APP_LOG(ID , LOG_TYPE, MDF_TABLE_NAME , OPERATE_TYPE ,BEFORE_RECORD , AFTER_RECORD , MENDER_NAME , CREATED_BY,CREATION_DATE )
       VALUES(SYS_GUID(), 'DB_TRIGGER', 'CF_DEPTUCORGANIZATION' ,'UPDATE', 'DEPTID='||CAST( :OLD.DEPTID AS VARCHAR2(1000))||',ORGANIZATIONID='||CAST(:OLD.ORGANIZATIONID AS VARCHAR2(1000))||',CREATEUSERID='||CAST ( :OLD.CREATEUSERID AS VARCHAR2(4000))||',CREATEDATE='||CAST ( :OLD.CREATEDATE AS VARCHAR2(4000)),'DEPTID='||CAST( :NEW.DEPTID AS VARCHAR2(1000))||',ORGANIZATIONID='||CAST(:NEW.ORGANIZATIONID AS VARCHAR2(1000))||',CREATEUSERID='||CAST ( :NEW.CREATEUSERID AS VARCHAR2(4000))||',CREATEDATE='||CAST ( :NEW.CREATEDATE AS VARCHAR2(4000))  , '', :NEW.MODIFYUSERID,sysdate );
       
       
           ELSIF DELETING then
       
           INSERT INTO SYS_APP_LOG(ID , LOG_TYPE, MDF_TABLE_NAME , OPERATE_TYPE ,BEFORE_RECORD , AFTER_RECORD , MENDER_NAME , CREATED_BY,CREATION_DATE )
       VALUES(SYS_GUID(), 'DB_TRIGGER', 'CF_DEPTUCORGANIZATION' ,'DELETE', 'DEPTID='||CAST( :OLD.DEPTID AS VARCHAR2(1000))||',ORGANIZATIONID='||CAST(:OLD.ORGANIZATIONID AS VARCHAR2(1000))||',CREATEUSERID='||CAST ( :OLD.CREATEUSERID AS VARCHAR2(4000))||',CREATEDATE='||CAST ( :OLD.CREATEDATE AS VARCHAR2(4000))  ,'', '', :OLD.MODIFYUSERID,sysdate );
       
       
    ELSE
        NULL;
    END IF;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
        
        
END;

posted @ 2017-05-16 16:24  甜菜波波  阅读(512)  评论(0编辑  收藏  举报