使用触发器为主键列赋值时依然要使用到序列,只不过将主键列的赋值转移到了“幕后”,不再需要用户关心。 下面创建INSERT触发器add_book_trigger,在向book表中添加数据时,该触发器将自动为book表的主键列bookid赋值(参见教材P241)。触发器创建如下: SQL> CONNECT bookstore/admin; SQL> CREATE OR REPLACE TRIGGER add_book_trigger Before insert On book For each row Begin If :new.bookid is null then Select book_seq.nextval into :new.bookid from dual; End if; End add_book_trigger; / 触发器创建好后,在向book表中添加新记录时就可以不再关心主键列bookid的赋值问题,例如使用如下语句向book表中添加图书: SQL>insert into book(booknumber,bookname,bookpress,bookprice,typeid,booktime) values ('TS100018', '远古的呼唤', '清华大学出版社',36,1,sysdate); 查询book表中是否已经成功地添加了此图书,如下: SQL>select * from book where booknumber='TS100018'; 下面依次创建其他6张表的触发器自动为主键赋值
时间: 2023-12-25 21:06:50 浏览: 24
好的,根据您的要求,我可以给出创建其他6张表的触发器自动为主键赋值的SQL语句和相关说明,如下所示:
1. student表的触发器:
```sql
CREATE OR REPLACE TRIGGER add_student_trigger
BEFORE INSERT ON student
FOR EACH ROW
BEGIN
IF :new.student_id IS NULL THEN
SELECT student_seq.nextval INTO :new.student_id FROM dual;
END IF;
END add_student_trigger;
```
2. teacher表的触发器:
```sql
CREATE OR REPLACE TRIGGER add_teacher_trigger
BEFORE INSERT ON teacher
FOR EACH ROW
BEGIN
IF :new.teacher_id IS NULL THEN
SELECT teacher_seq.nextval INTO :new.teacher_id FROM dual;
END IF;
END add_teacher_trigger;
```
3. course表的触发器:
```sql
CREATE OR REPLACE TRIGGER add_course_trigger
BEFORE INSERT ON course
FOR EACH ROW
BEGIN
IF :new.course_id IS NULL THEN
SELECT course_seq.nextval INTO :new.course_id FROM dual;
END IF;
END add_course_trigger;
```
4. class表的触发器:
```sql
CREATE OR REPLACE TRIGGER add_class_trigger
BEFORE INSERT ON class
FOR EACH ROW
BEGIN
IF :new.class_id IS NULL THEN
SELECT class_seq.nextval INTO :new.class_id FROM dual;
END IF;
END add_class_trigger;
```
5. score表的触发器:
```sql
CREATE OR REPLACE TRIGGER add_score_trigger
BEFORE INSERT ON score
FOR EACH ROW
BEGIN
IF :new.score_id IS NULL THEN
SELECT score_seq.nextval INTO :new.score_id FROM dual;
END IF;
END add_score_trigger;
```
6. lend表的触发器:
```sql
CREATE OR REPLACE TRIGGER add_lend_trigger
BEFORE INSERT ON lend
FOR EACH ROW
BEGIN
IF :new.lend_id IS NULL THEN
SELECT lend_seq.nextval INTO :new.lend_id FROM dual;
END IF;
END add_lend_trigger;
```
以上就是为其他6张表创建的自动为主键赋值的触发器的SQL语句和相关说明,希望对您有所帮助。需要注意的是,为了确保触发器能够自动为主键赋值,需要确保序列已经创建并正确配置。