此处的触发器需求为跨库操作,当A库的user_course_study_info表发生变化时,反映到lengine库的user_course_study_info表也做同样的变化。
使用上文的dblink来连接另一个数据库B;
这个触发器的条件为,当A库的表中进行insert、updata、delete时,如果course_number对应的course_type=1,还有uuid='HEYDIMZXHFTDGMJVGQZDSNI',就对lengine库的同名表进行同样的操作。
create or replace trigger uc_study_info_to_lengine after insert or update or delete
on USER_COURSE_STUDY_INFO for each row
declare
integrity_error exception;
errno integer;
errmsg char(200);
ctype integer;
begin
select course_type into ctype from course where course_number=:NEW.COURSE_NUMBER;
if ctype=1 or ctype=2 then
if inserting then
if :NEW.uuid='HEYDIMZXHFTDGMJVGQZDSNI' then
insert into user_course_study_info@dblink_test01(ID,ATTEMPT_NUM,BROWSE_COMPLETED_TIME,BROWSE_SCORE,COURSE_NUMBER,FIRST_DATE,high_status,high_status_date,high_score,last_date,last_status,last_score,student_id,student_from,total_time,total_time_minite)
values(:NEW.ID,:NEW.ATTEMPT_NUM,:NEW.BROWSE_COMPLETED_TIME,:new.BROWSE_SCORE,:NEW.COURSE_NUMBER,:NEW.FIRST_DATE,:NEW.high_status,:NEW.high_status_date,:NEW.high_score,:NEW.last_date,:NEW.last_status,:NEW.last_score,:NEW.student_id,:NEW.student_from,:NEW.total_time,:NEW.total_time_minite);
end if;
elsif updating then
if :OLD.uuid='HEYDIMZXHFTDGMJVGQZDSNI' then
update user_course_study_info@dblink_test01 set ID=:NEW.ID,ATTEMPT_NUM=:NEW.ATTEMPT_NUM,BROWSE_COMPLETED_TIME=:NEW.BROWSE_COMPLETED_TIME,BROWSE_SCORE=:NEW.BROWSE_SCORE, COURSE_NUMBER=:NEW.COURSE_NUMBER,FIRST_DATE=:NEW.FIRST_DATE, high_status=:NEW.high_status,
high_status_date=:NEW.high_status_date,high_score=:NEW.high_score,last_date=:NEW.last_date,last_status=:NEW.last_status,last_score=:NEW.last_score,student_id=:NEW.student_id,student_from=:NEW.student_from,total_time=:NEW.total_time,total_time_minite=:NEW.total_time_minite
where id=:OLD.id;
end if;
elsif deleting then
if :OLD.uuid='HEYDIMZXHFTDGMJVGQZDSNI' then
delete from user_course_study_info@dblink_test01 where id=:OLD.id;
end if;
end if;
end if;
exception
when integrity_error then
raise_application_error(errno, errmsg);
end;