我是ORACLE的新手昨天写了个想让主键自增的触发器(序列已写好):
create trigger users_tb_tri
before insert on USERS
for each row begin
select users_tb_seq.nextval into :new.ID from dual
end;
commit;
执行时报编译错误。ERROR信息为:ORA-00900:无效的SQL语句单独执行SQL语句(第四行)的话,报错: ORA-01008: 并非所有变量都已绑定请问我的那句SQL语句哪错了? 顺便问一下:ORACLE中都是要每个表对应一个触发器来完成主键自增的吗? 这样岂不是很麻烦?
:new
CREATE OR REPLACE TRIGGER mCategory_trig_autoinc
BEFORE INSERT ON mvnforumCategory
FOR EACH ROW
BEGIN
IF (:new.CategoryID IS NULL) THEN
SELECT mvnforumCategory_seq.nextval INTO :new.CategoryID FROM DUAL;
END IF;
END;
/
--创建自增列
--表
create table ROI_DATAMODEL
(
ID integer not null,
NAME VARCHAR2(100),
CONTEXT CLOB,
PRIMARYKEYS VARCHAR2(200),
PRIMARY KEY(ID)
);
--序列
CREATE SEQUENCE seq_ROI_DATAMODEL START WITH 1 INCREMENT BY 1;
--设置触发器
CREATE OR REPLACE TRIGGER tri_ROI_DATAMODEL
BEFORE INSERT
ON ROI_DATAMODEL
FOR EACH ROW
BEGIN
select seq_ROI_DATAMODEL.nextval into :new.id from dual;
END;
--测试
INSERT INTO ROI_DATAMODEL(NAME,CONTEXT) VALUES('guangshun','son');
INSERT INTO ROI_DATAMODEL(NAME,CONTEXT) VALUES('guangshun','wife');SELECT * FROM ROI_DATAMODEL;