触发器的问题

soho00222 2006-08-23 10:57:11

DROP TABLE EM_EXAM;

DROP TABLE NM_NEWS;

DROP TABLE NM_TYPE;

DROP TABLE PM_PATCH;

DROP TABLE SPM_FILE;

DROP TABLE SPM_KEYWORDS;

DROP TABLE SPM_TYPETREE;

DROP SEQUENCE S_EM_EXAM;

DROP SEQUENCE S_NM_NEWS;

DROP SEQUENCE S_NM_TYPE;

DROP SEQUENCE S_PM_PATCH;

DROP TRIGGER T_EM_EXAM;

DROP TRIGGER T_NM_NEWS;

DROP TRIGGER T_NM_TYPE;

DROP TRIGGER T_PM_PATCH;

create table EM_EXAM
(
PK_EXAMID NUMBER(10) not null,
SUBJECT VARCHAR2(500),
OPTION1 VARCHAR2(500),
OPTION2 VARCHAR2(500),
OPTION3 VARCHAR2(500),
OPTION4 VARCHAR2(500),
EXAMTYPE NUMBER(10),
ADDTIME DATE,
UPLOADUSER NUMBER(10),
ANSWER1 VARCHAR2(50),
ANSWER2 VARCHAR2(50),
ANSWER3 VARCHAR2(50)
);

-- Create sequence
create sequence S_EM_EXAM
minvalue 1
maxvalue 9999999999999999999999999999
start with 101
increment by 1
cache 20;

create table NM_NEWS
(
PK_NEWSID NUMBER(10) not null,
FK_TYPEID NUMBER(10),
SUBJECT VARCHAR2(500),
CONTENT VARCHAR2(4000),
ADDTIME DATE,
UPLOADUSER NUMBER(10)
);

create sequence S_NM_NEWS
minvalue 1
maxvalue 9999999999999999999999999999
start with 81
increment by 1
cache 20;

create table NM_TYPE
(
PK_TYPEID NUMBER(10) not null,
TYPENAME VARCHAR2(500) not null
);

create sequence S_NM_TYPE
minvalue 1
maxvalue 9999999999999999999999999999
start with 81
increment by 1
cache 20;

create table PM_PATCH
(
PK_PATCHID NUMBER(10) not null,
SUBJECT VARCHAR2(500),
CONTENT VARCHAR2(4000),
ADDTIME DATE,
PATCHFILE BLOB,
UPLOADUSER NUMBER(10),
FILETYPE VARCHAR2(50),
FILENAME VARCHAR2(500)
);

create sequence S_PM_PATCH
minvalue 1
maxvalue 9999999999999999999999999999
start with 81
increment by 1
cache 20;

create table SPM_FILE
(
FK_CHILDID NUMBER(10) not null,
FILENAME VARCHAR2(500),
FILESIZE NUMBER(10) not null,
ADDTIME DATE not null,
FILECONTENT BLOB
);

create table SPM_KEYWORDS
(
FK_CHILDID NUMBER(10) not null,
KEYWORDS VARCHAR2(500)
);

create table SPM_TYPETREE
(
PK_CHILDID NUMBER(10) not null,
FK_PARENTID NUMBER(10) not null,
NAME VARCHAR2(500) not null,
DEPATH NUMBER(10) not null,
CHILD NUMBER(10) not null,
ROOTID NUMBER(10) not null,
SORT NUMBER(10) not null,
PARENTSTR VARCHAR2(500),
CONTENT VARCHAR2(4000),
IFPASS NUMBER(10) default 0 not null
);

CREATE OR REPLACE TRIGGER "T_EM_EXAM" BEFORE
INSERT ON "EM_EXAM" FOR EACH ROW
declare
integrity_error exception;
errno integer;
errmsg char(200);
dummy integer;
found boolean;

begin
select S_EM_EXAM.NEXTVAL INTO :new.PK_EXAMId from dual;
-- Errors handling
exception
when integrity_error then
raise_application_error(errno, errmsg);
end;

create sequence S_NM_NEWS
minvalue 1
maxvalue 9999999999999999999999999999
start with 61
increment by 1
cache 20;

CREATE OR REPLACE TRIGGER "T_NM_NEWS" BEFORE
INSERT ON "NM_NEWS" FOR EACH ROW
declare
integrity_error exception;
errno integer;
errmsg char(200);
dummy integer;
found boolean;

begin
select S_NM_NEWS.NEXTVAL INTO :new.PK_NEWSId from dual;

-- Errors handling
exception
when integrity_error then
raise_application_error(errno, errmsg);
end;

CREATE OR REPLACE TRIGGER "T_NM_TYPE" BEFORE
INSERT ON "NM_TYPE" FOR EACH ROW
declare
integrity_error exception;
errno integer;
errmsg char(200);
dummy integer;
found boolean;

begin
select S_NM_TYPE.NEXTVAL INTO :new.PK_TypeId from dual;

-- Errors handling
exception
when integrity_error then
raise_application_error(errno, errmsg);
end;

CREATE OR REPLACE TRIGGER "T_PM_PATCH" BEFORE
INSERT ON "PM_PATCH" FOR EACH ROW
declare
integrity_error exception;
errno integer;
errmsg char(200);
dummy integer;
found boolean;

begin
select S_PM_PATCH.NEXTVAL INTO :new.PK_PATCHId from dual;

-- Errors handling
exception
when integrity_error then
raise_application_error(errno, errmsg);
end;

在PL/SQL中，执行上段语句，在触发器这块出问题了，老是给出一个莫名其妙的数，例如18，26，去掉触发器建表和建序列都没有问题，其实触发器也是正确的，是我从PL/SQL里面拷贝出来的，但是为什么就是建立不了呢？

...全文