oracle中自增长的问题
SQL code
--创建学生表
create table student(
stuid int primary key , --主键,想设置为自增长
sname varchar(10),
sex number(1),
birthday date
);
-- 建立序列:
create sequence stu_sequence
minvalue 1
maxvalue 99999999999999999999999999
start with 1
increment by 1
cache 20;
-- 建立触发器,写法1
CREATE OR REPLACE TRIGGER "stu_trigger" BEFORE
INSERT ON student FOR EACH ROW
declare
next_checkup_no number;
begin
select stu_sequence.nextval
into next_checkup_no
from dual;
:NEW.stuid := next_checkup_no;
end;
-- 建立触发器,写法2
create or replace trigger "stu_trigger"
before insert on student for each row
begin
select stu_sequence.nextval into :new.stuid from dual;
end;
--插入数据1
insert into student values(1,'riyun',0,sysdate);
--插入数据2
insert into student values('tom',0,sysdate);
问题:1、建立触发器的写法1和2有什么不同?
2、:NEW.stuid := next_checkup_no这里的“:”代表什么意思?
3、当执行插入数据1的时候oracle报“该触发器无效且未通过重新验证”错误?
4、当执行插入数据2的时候oracle报“没有足够的值”错误?
请大侠们帮忙看下指点迷津!!3q
------解决方案--------------------1.写法不同而已,1中有局部变量,2中没有
2.:new中的:是一个整体,指新增的行,:=是指对变是赋值,也是一个整体
3.这个说明建立的触发器有问题
4.你的表有四列而你只入了三列,至少应是,这样列才匹配,但能不能执行是另一回事
insert into student(sname,sex,birthday) values('tom',0,sysdate);
------解决方案--------------------CREATE OR REPLACE TRIGGER "TRI_DATAID" BEFORE INSERT ON "TIGER"."WEB_DATA" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW
DECLARE last_Sequence NUMBER; last_InsertID NUMBER; factor NUMBER; BEGIN IF (:NEW."DATAID" IS NULL) THEN SELECT "DATA_0".NEXTVAL INTO :NEW."DATAID" FROM DUAL; ELSE SELECT Last_Number-1 INTO last_Sequence FROM User_Sequences WHERE UPPER(Sequence_Name) = UPPER('DATA_0'); SELECT :NEW."DATAID" INTO last_InsertID FROM DUAL; WHILE (last_InsertID > last_Sequence) LOOP SELECT "DATA_0".NEXTVAL INTO last_Sequence FROM DUAL; END LOOP; END IF; END;