oracle触发器一例

      此处的触发器需求为跨库操作，当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;