--创建学生表
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
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
2.:new中的:是一个整体,指新增的行,:=是指对变是赋值,也是一个整体
3.这个说明建立的触发器有问题
4.你的表有四列而你只入了三列,至少应是,这样列才匹配,但能不能执行是另一回事
insert into student(sname,sex,birthday) values('tom',0,sysdate);
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;
--你这触发器有问题吗?我试了下,好像没问题,还真想不通唉,呵呵
--LZ太不厚道了,解决了也不分享下解决方案SQL> create table student(
2 stuid int primary key , --主键,想设置为自增长
3 sname varchar(10),
4 sex number(1),
5 birthday date
6 );表已创建。SQL>
SQL> create sequence stu_sequence
2 minvalue 1
3 maxvalue 99999999999999999999999999
4 start with 1
5 increment by 1
6 cache 20;序列已创建。SQL>
SQL> CREATE OR REPLACE TRIGGER "stu_trigger" BEFORE
2 INSERT ON student FOR EACH ROW
3 declare
4 next_checkup_no number;
5 begin
6 select stu_sequence.nextval
7 into next_checkup_no
8 from dual;
9 :NEW.stuid := next_checkup_no;
10 end;
11 /触发器已创建SQL> insert into student values(1,'riyun',0,sysdate);已创建 1 行。SQL> -- 建立触发器,写法2
SQL> create or replace trigger "stu_trigger"
2 before insert on student for each row
3 begin
4 select stu_sequence.nextval into :new.stuid from dual;
5 end;
6
7 /触发器已创建SQL> insert into student values(1,'riyun',0,sysdate);已创建 1 行。