/**用户user*/
/**创建用户表*/
CREATE TABLE teacher(
id INT PRIMARY KEY,
username VARCHAR(20),
password VARCHAR(20)
);
/**为用户id设置自增*/
/**创建序列*/
CREATE SEQUENCE seq_teacher_id
INCREMENT BY 1
START WITH 1
MAXVALUE 99999999;
/**创建触发器*/
CREATE OR REPLACE TRIGGER teacher_id_trigger
BEFORE INSERT ON teacher
FOR EACH ROW
BEGIN
SELECT seq_teacher_id.NEXTVAL INTO :new.id FROM teacher;
END teacher_id_trigger; /**插入数据*/
INSERT INTO teacher VALUES(seq_teacher_id.nextval,"yaofeihu","19861015");
INSERT INTO teacher VALUES(seq_teacher_id.nextval,"wenyou","521");
我插入下面两行时出现 列在此处不允许(*号出现在第三列下边)
/**创建用户表*/
CREATE TABLE teacher(
id INT PRIMARY KEY,
username VARCHAR(20),
password VARCHAR(20)
);
/**为用户id设置自增*/
/**创建序列*/
CREATE SEQUENCE seq_teacher_id
INCREMENT BY 1
START WITH 1
MAXVALUE 99999999;
/**创建触发器*/
CREATE OR REPLACE TRIGGER teacher_id_trigger
BEFORE INSERT ON teacher
FOR EACH ROW
BEGIN
SELECT seq_teacher_id.NEXTVAL INTO :new.id FROM teacher;
END teacher_id_trigger; /**插入数据*/
INSERT INTO teacher VALUES(seq_teacher_id.nextval,"yaofeihu","19861015");
INSERT INTO teacher VALUES(seq_teacher_id.nextval,"wenyou","521");
我插入下面两行时出现 列在此处不允许(*号出现在第三列下边)
id Number PRIMARY KEY,
username VARCHAR2(20),
password VARCHAR2(20)
);
试试看
触发器
1.SELECT seq_teacher_id.nextval INTO :new.id FROM dual;
插入数据
2.INSERT INTO teacher(username,password) VALUES('yaofeihu','19861015');
这样改就没问题了
你在插入字符串的时候不能用双引号,双引号在oracle中会被认为是字段名,只能用单引号:
INSERT INTO teacher VALUES(seq_teacher_id.nextval,'yaofeihu','19861015');
INSERT INTO teacher VALUES(seq_teacher_id.nextval,'wenyou','521');
SELECT seq_teacher_id.nextval INTO :new.id FROM dual;
只要有一个就行
还有应该用单引号
SQL> CREATE TABLE teacher(
2 id INT PRIMARY KEY,
3 username VARCHAR(20),
4 password VARCHAR(20)
5 );Table createdSQL>
SQL> /**ΪÓû§idÉèÖÃ×ÔÔö*/
2 /**´´½¨ÐòÁÐ*/
3 CREATE SEQUENCE seq_teacher_id
4 INCREMENT BY 1
5 START WITH 1
6 MAXVALUE 99999999;Sequence createdSQL>
SQL> /**????*/
2 INSERT INTO teacher VALUES(seq_teacher_id.nextval,'yaofeihu','19861015');1 row insertedSQL> INSERT INTO teacher VALUES(seq_teacher_id.nextval,'wenyou','521');1 row insertedSQL> commit;Commit completeSQL> select * from teacher; ID USERNAME PASSWORD
--------------------------------------- -------------------- --------------------
1 yaofeihu 19861015
2 wenyou 521SQL>
SQL> drop table teacher;Table droppedSQL> drop SEQUENCE seq_teacher_id;Sequence droppedSQL> /**ΪÓû§idÉèÖÃ×ÔÔö*/
2 /**´´½¨ÐòÁÐ*/
3 CREATE SEQUENCE seq_teacher_id
4 INCREMENT BY 1
5 START WITH 1
6 MAXVALUE 99999999;Sequence createdSQL> /**Óû§user*/
2 /**´´½¨Óû§±í*/
3 CREATE TABLE teacher(
4 id INT PRIMARY KEY,
5 username VARCHAR(20),
6 password VARCHAR(20)
7 );Table createdSQL> CREATE OR REPLACE TRIGGER teacher_id_trigger
2 BEFORE INSERT ON teacher
3 FOR EACH ROW
4 BEGIN
5 select seq_teacher_id.nextval into :new.id from dual;
6
7 END teacher_id_trigger;
8 /Trigger createdSQL> /**²åÈëÊý¾Ý*/
2 INSERT INTO teacher (username,password)VALUES('yaofeihu','19861015');1 row insertedSQL> INSERT INTO teacher (username,password)VALUES('wenyou','521');1 row insertedSQL> commit;Commit completeSQL> select * from teacher; ID USERNAME PASSWORD
--------------------------------------- -------------------- --------------------
1 yaofeihu 19861015
2 wenyou 521SQL>
INSERT INTO teacher VALUES(seq_teacher_id.nextval,"yaofeihu","19861015");
INSERT INTO teacher VALUES(seq_teacher_id.nextval,"wenyou","521");
应该改为:
INSERT INTO teacher VALUES(seq_teacher_id.nextval,'yaofeihu','19861015');
INSERT INTO teacher VALUES(seq_teacher_id.nextval,'wenyou','521'); 问题2:
/**创建触发器*/
CREATE OR REPLACE TRIGGER teacher_id_trigger
BEFORE INSERT ON teacher
FOR EACH ROW
BEGIN
SELECT seq_teacher_id.NEXTVAL INTO :new.id FROM teacher;
END teacher_id_trigger;
应该改为:
CREATE OR REPLACE TRIGGER teacher_id_trigger
BEFORE INSERT ON teacher
FOR EACH ROW
BEGIN
SELECT seq_teacher_id.NEXTVAL INTO :new.id FROM dual;
END teacher_id_trigger;