昨天做了一次数据库expdp/impdp迁移，导入之后发现有个触发器报错：
begin
if inserting then
if :new.ptzz = 1 then
insert into MVS_R_SCHOOL_SPECIALTY_TYPE
(Id, SPECIALID, STUDY_TYPEID, STUDY_TYPENAME)
values
(seq_MVS_SPECIALTY_TYPE.Nextval, :new.id, 1, '普通中专');
end if;
if :new.crzz = 1 then
insert into MVS_R_SCHOOL_SPECIALTY_TYPE
(Id, SPECIALID, STUDY_TYPEID, STUDY_TYPENAME)
values
(seq_MVS_SPECIALTY_TYPE.Nextval, :new.id, 2, '成人中专');
end if;
if :new.zygz = 1 then
insert into MVS_R_SCHOOL_SPECIALTY_TYPE
(Id, SPECIALID, STUDY_TYPEID, STUDY_TYPENAME)
values
(seq_MVS_SPECIALTY_TYPE.Nextval, :new.id, 3, '职业高中');
end if;
if :new.jgxx = 1 then
insert into MVS_R_SCHOOL_SPECIALTY_TYPE
(Id, SPECIALID, STUDY_TYPEID, STUDY_TYPENAME)
values
(seq_MVS_SPECIALTY_TYPE.Nextval, :new.id, 4, '技工学校');
end if;
if :new.qt = 1 then
insert into MVS_R_SCHOOL_SPECIALTY_TYPE
(Id, SPECIALID, STUDY_TYPEID, STUDY_TYPENAME)
values
(seq_MVS_SPECIALTY_TYPE.Nextval, :new.id, 5, '其他');
end if;
end if;
if updating and ((:new.ptzz != :old.ptzz) or (:new.crzz != :old.crzz) or
(:new.zygz != :old.zygz) or (:new.jgxx != :old.jgxx) or
(:new.qt != :old.qt)) then
if :new.ptzz = 1 and :old.ptzz = 0 then
insert into MVS_R_SCHOOL_SPECIALTY_TYPE
(Id, SPECIALID, STUDY_TYPEID, STUDY_TYPENAME)
values
(seq_MVS_SPECIALTY_TYPE.Nextval, :new.id, 1, '普通中专');
elsif :new.ptzz = 0 and :old.ptzz = 1 then
delete from MVS_R_SCHOOL_SPECIALTY_TYPE
where SPECIALID = :old.id
and STUDY_TYPEID = 1;
end if;
if :new.crzz = 1 and :old.crzz = 0 then
insert into MVS_R_SCHOOL_SPECIALTY_TYPE
(Id, SPECIALID, STUDY_TYPEID, STUDY_TYPENAME)
values
(seq_MVS_SPECIALTY_TYPE.Nextval, :new.id, 2, '成人中专');
elsif :new.crzz = 0 and :old.crzz = 1 then
delete from MVS_R_SCHOOL_SPECIALTY_TYPE
where SPECIALID = :old.id
and STUDY_TYPEID = 2;
end if;
if :new.zygz = 1 and :old.zygz = 0 then
insert into MVS_R_SCHOOL_SPECIALTY_TYPE
(Id, SPECIALID, STUDY_TYPEID, STUDY_TYPENAME)
values
(seq_MVS_SPECIALTY_TYPE.Nextval, :new.id, 3, '职业高中');
elsif :new.zygz = 0 and :old.zygz = 1 then
delete from MVS_R_SCHOOL_SPECIALTY_TYPE
where SPECIALID = :old.id
and STUDY_TYPEID = 3;
end if;
if :new.jgxx = 1 and :old.jgxx = 0 then
insert into MVS_R_SCHOOL_SPECIALTY_TYPE
(Id, SPECIALID, STUDY_TYPEID, STUDY_TYPENAME)
values
(seq_MVS_SPECIALTY_TYPE.Nextval, :new.id, 4, '技工学校');
elsif :new.jgxx = 0 and :old.jgxx = 1 then
delete from MVS_R_SCHOOL_SPECIALTY_TYPE
where SPECIALID = :old.id
and STUDY_TYPEID = 4;
end if;
if :new.qt = 1 and :old.qt = 0 then
insert into MVS_R_SCHOOL_SPECIALTY_TYPE
(Id, SPECIALID, STUDY_TYPEID, STUDY_TYPENAME)
values
(seq_MVS_SPECIALTY_TYPE.Nextval, :new.id, 5, '其他');
elsif :new.qt = 0 and :old.qt = 1 then
delete from MVS_R_SCHOOL_SPECIALTY_TYPE
where SPECIALID = :old.id
and STUDY_TYPEID = 5;
end if;
end if;
--begin--
if updating and
(:new.STANDARD_SPECIALTY_ID != :old.STANDARD_SPECIALTY_ID) and
(get_student_sfsn2(:new.STANDARD_SPECIALTY_ID) !=
get_student_sfsn2(:old.STANDARD_SPECIALTY_ID)) then
update mvs_b_student t
set t.is_update = 1
where t.SPECIALIZEDID = :old.id
and t.status = 1;
end if;
---end---
end modify_school_specialitytype;
错误内容如下：
行号= 4 列号= 7 错误文本= PL/SQL: SQL Statement ignored
行号= 7 列号= 10 错误文本= PL/SQL: ORA-02289: sequence does not exist
行号= 10 列号= 7 错误文本= PL/SQL: SQL Statement ignored
行号= 13 列号= 10 错误文本= PL/SQL: ORA-02289: sequence does not exist
行号= 16 列号= 7 错误文本= PL/SQL: SQL Statement ignored
行号= 19 列号= 10 错误文本= PL/SQL: ORA-02289: sequence does not exist
行号= 22 列号= 7 错误文本= PL/SQL: SQL Statement ignored
行号= 25 列号= 10 错误文本= PL/SQL: ORA-02289: sequence does not exist
行号= 28 列号= 7 错误文本= PL/SQL: SQL Statement ignored
行号= 31 列号= 10 错误文本= PL/SQL: ORA-02289: sequence does not exist
行号= 38 列号= 7 错误文本= PL/SQL: SQL Statement ignored
行号= 41 列号= 10 错误文本= PL/SQL: ORA-02289: sequence does not exist
行号= 49 列号= 7 错误文本= PL/SQL: SQL Statement ignored
行号= 52 列号= 10 错误文本= PL/SQL: ORA-02289: sequence does not exist
行号= 60 列号= 7 错误文本= PL/SQL: SQL Statement ignored
行号= 63 列号= 10 错误文本= PL/SQL: ORA-02289: sequence does not exist
行号= 71 列号= 7 错误文本= PL/SQL: SQL Statement ignored
行号= 74 列号= 10 错误文本= PL/SQL: ORA-02289: sequence does not exist
行号= 82 列号= 7 错误文本= PL/SQL: SQL Statement ignored
行号= 85 列号= 10 错误文本= PL/SQL: ORA-02289: sequence does not exist
个人对触发器存储过程还不太熟，请高手帮忙看看。
[ 本帖最后由 lizisor1 于 2011-4-6 09:32 编辑 ]
|
