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;
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/