创建表:
-- Create table
create table JR_KHZHYEGL
(
ID NUMBER not null,
HYDWNO VARCHAR2(30) not null,
QCJE VARCHAR2(50),
RJE VARCHAR2(50),
SHJE VARCHAR2(50),
FSDATE VARCHAR2(20),
CZYNO VARCHAR2(30),
LRDATE VARCHAR2(20),
CJE VARCHAR2(50)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column JR_KHZHYEGL.ID
is '客户主键';
comment on column JR_KHZHYEGL.QCJE
is '账户余额';
comment on column JR_KHZHYEGL.RJE
is '入账金额';
comment on column JR_KHZHYEGL.SHJE
is '赎货金额';
comment on column JR_KHZHYEGL.FSDATE
is '发生日期';
comment on column JR_KHZHYEGL.CZYNO
is '录入人员';
comment on column JR_KHZHYEGL.LRDATE
is '录入日期';
comment on column JR_KHZHYEGL.CJE
is '出账金额';
-- Create/Recreate primary, unique and foreign key constraints
alter table JR_KHZHYEGL
add constraint SYS_JR_KHZHYEGL primary key (ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
创建序列:
CREATE SEQUENCE jr_khzhyegl_id_seq
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加,不循环
NOCACHE -- 不建缓冲区
创建触发器;
CREATE TRIGGER jr_khzhyegl_trigger BEFORE
INSERT ON jr_khzhyegl FOR EACH ROW WHEN(new.id is null)
begin
select jr_khzhyegl_id_seq.nextval into:new.id from dual;
end;
-------------------
插入语句:insert into jr_khzhyegl(hydwno,qcje,rje) values('05161','jskld','sdjsdl')
插入语句时报错:ORA-04098: 触发器 “wl201112.jr_khzhyegl_trigger” 无效且未通过重新确认
-- Create table
create table JR_KHZHYEGL
(
ID NUMBER not null,
HYDWNO VARCHAR2(30) not null,
QCJE VARCHAR2(50),
RJE VARCHAR2(50),
SHJE VARCHAR2(50),
FSDATE VARCHAR2(20),
CZYNO VARCHAR2(30),
LRDATE VARCHAR2(20),
CJE VARCHAR2(50)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column JR_KHZHYEGL.ID
is '客户主键';
comment on column JR_KHZHYEGL.QCJE
is '账户余额';
comment on column JR_KHZHYEGL.RJE
is '入账金额';
comment on column JR_KHZHYEGL.SHJE
is '赎货金额';
comment on column JR_KHZHYEGL.FSDATE
is '发生日期';
comment on column JR_KHZHYEGL.CZYNO
is '录入人员';
comment on column JR_KHZHYEGL.LRDATE
is '录入日期';
comment on column JR_KHZHYEGL.CJE
is '出账金额';
-- Create/Recreate primary, unique and foreign key constraints
alter table JR_KHZHYEGL
add constraint SYS_JR_KHZHYEGL primary key (ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
创建序列:
CREATE SEQUENCE jr_khzhyegl_id_seq
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加,不循环
NOCACHE -- 不建缓冲区
创建触发器;
CREATE TRIGGER jr_khzhyegl_trigger BEFORE
INSERT ON jr_khzhyegl FOR EACH ROW WHEN(new.id is null)
begin
select jr_khzhyegl_id_seq.nextval into:new.id from dual;
end;
-------------------
插入语句:insert into jr_khzhyegl(hydwno,qcje,rje) values('05161','jskld','sdjsdl')
插入语句时报错:ORA-04098: 触发器 “wl201112.jr_khzhyegl_trigger” 无效且未通过重新确认
解决方案 »
- ant脚本运行sql出错,但是sql语句没有任何问题
- 帮忙解释下一个SQL函数的意思.
- 各位xdjm,看看这个oracle sql有什么优化的方面
- plsql 高手 在线等
- 多用户同时超作同一个表问题。
- *****sql中根据某字段将另一字段相连***菜鸟求助*****
- oracle启动问题
- linux9安装oracle9i的问题??急
- OraclsTNSListener80无法启动,WHY?
- 紧急求助,oracle中的工具,R6 有没有人用过,实干什么用的!请大家给于帮助,很急!
- Dec 20 2011 9:58AM 怎么插入到oracle中的date类型的字段中
- GoldenGate十大误区【转】
(
ID NUMBER not null,
HYDWNO VARCHAR2(30) not null,
QCJE VARCHAR2(50),
RJE VARCHAR2(50),
SHJE VARCHAR2(50),
FSDATE VARCHAR2(20),
CZYNO VARCHAR2(30),
LRDATE VARCHAR2(20),
CJE VARCHAR2(50)
);CREATE SEQUENCE jr_khzhyegl_id_seq
INCREMENT BY 1 START WITH 1
NOMAXVALUE NOCYCLE NOCACHE;
触发器代码:CREATE TRIGGER jr_khzhyegl_trigger BEFORE
INSERT ON jr_khzhyegl FOR EACH ROW WHEN(new.id is null)
BEGIN
SELECT jr_khzhyegl_id_seq.nextval INTO :new.id FROM dual;
END;
实测结果:
问题所在:
你的触发器代码中:into:new.id,into和:new.id中缺少一个空格。
2 increment by 1 -- 每次递增1
3 start with 1 -- 从1开始
4 nomaxvalue -- 没有最大值
5 minvalue 1 -- 最小值=1
6 NOCYCLE; -- 不循环Sequence created.
SQL> CREATE TABLE test_create_tab2 (
2 id INT,
3 val VARCHAR(10),
4 PRIMARY KEY (id)
5 );
http://zhidao.baidu.com/question/314595399.html
2 BEFORE INSERT ON test_create_tab2
3 FOR EACH ROW
4 BEGIN
5 SELECT test_sequence2.nextval INTO :new.id FROM dual;
6 END;
7 /Trigger created.SQL> INSERT INTO test_create_tab2(val) VALUES ('NO id');1 row created.SQL> INSERT INTO test_create_tab2(id, val) VALUES (1, 'id no use');1 row created.SQL> SELECT * FROM test_create_tab2; ID VAL
---------- --------------------
1 NO id
2 id no use
INSERT INTO JR_KHZHYEGL(HYDWNO) VALUES('123'); SELECT * FROM JR_KHZHYEGL T ;试试没得问题啊
INSERT ON jr_khzhyegl
FOR EACH ROW when (new.ID is null)
begin
INSERT INTO TEST_USER AS select JR_KHZHYEGL_ID_SEQ.nextval into :new.ID from dual;
end;你的jr_khzhyegl_trigger 还能编译通过啊?
但是一执行insert into jr_khzhyegl(hydwno) values('552662')还是报
ORA-04098: 触发器 “wl201112.jr_khzhyegl_trigger” 无效且未通过重新确认
create or replace trigger jr_khzhyegl_trigger
before insert on jr_khzhyegl
for each row
when(new.id is null)begin
select jr_khzhyegl_id_seq.nextval into :new.id from dual;
end jr_khzhyegl_trigger;
好像真的有空格 编译没有通过
手打了一遍 没有问题了 楼主再看看