一个TRIGGER

CREATE OR REPLACE TRIGGER tri_to_sns_edu AFTER INSERT OR UPDATE OR DELETE ON tbl_edu_exp
REFERENCING OLD AS old_value NEW AS new_value
FOR EACH ROW
DECLARE
v_school_name VARCHAR2(30 CHAR);
v_school_address VARCHAR2(100 CHAR);
vs_msg VARCHAR2(600);
BEGIN
 
    IF INSERTING THEN 
      SELECT NAME,area_id INTO v_school_name,v_school_address from tbl_school WHERE school_id=:new_value.school_id;          
       INSERT INTO sns.sns_user_edu VALUES(:new_value.exp_id,v_school_name,0,v_school_address,:new_value.starttime,:new_value.endtime,:new_value.member_id,0);
       END IF;
   IF UPDATING THEN 
     SELECT NAME,area_id INTO v_school_name,v_school_address from tbl_school WHERE school_id=:new_value.school_id;                 
     UPDATE sns.sns_user_edu SET SCHOOL_NAME=v_school_name,SCHOOL_TYPE=0,ADDRESS=v_school_address,START_TIME=:new_value.starttime,END_TIME=:new_value.endtime,USER_ID=:new_value.member_id,EVENT=1 WHERE EDU_ID=:old_value.exp_id;
     END IF;
   IF DELETING THEN
      DELETE from sns.sns_user_edu WHERE edu_id=:old_value.exp_id;       
     END IF;
  
  /******************************************************************/
     --错误处理部分
     EXCEPTION
       WHEN OTHERS THEN
           vs_msg := '出现异常：'||SUBSTR(SQLERRM,1,500);
     --把当前错误记录进日志表。
       INSERT INTO TBL_ERROR(ERR_DATE,ERR_TEXT)  VALUES(SYSDATE,vs_msg);
    RETURN;
END;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21158541/viewspace-668505/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21158541/viewspace-668505/