我这里有两张表:
--LM_Books表
create table LM_Books(
id number(9) not null primary key, --书的ID号
bookName varchar2(50) not null, --书名
intro varchar2(500), --书的简介
type varchar2(60) not null, --书的类型
author varchar2(40) not null, --书的作者
publisher varchar2(80) not null, --书的出版社
hold int default 0 check(hold in (0,1)), --是否被借出
addTime date default sysdate, --上架时间
loanTimes int default 0); --借出次数--表LM_loanbooks
--用来储存被借出书籍的信息
create table LM_loanbooks(
id number(10) not null primary key, --id号
loanee varchar2(25) not null references lm_users(username), --债务人
booksId number(9) not null references lm_books(id), --被借书的ID号
lentTime date not null default sysdate, --借出时间
ruturnTime date not null default sysdate+30); --应还时间现在我就是想每当添加一条LM_loanbooks记录,与之相关的,被借出的书的借出次数就+1
请问这个怎么去实现?
--LM_Books表
create table LM_Books(
id number(9) not null primary key, --书的ID号
bookName varchar2(50) not null, --书名
intro varchar2(500), --书的简介
type varchar2(60) not null, --书的类型
author varchar2(40) not null, --书的作者
publisher varchar2(80) not null, --书的出版社
hold int default 0 check(hold in (0,1)), --是否被借出
addTime date default sysdate, --上架时间
loanTimes int default 0); --借出次数--表LM_loanbooks
--用来储存被借出书籍的信息
create table LM_loanbooks(
id number(10) not null primary key, --id号
loanee varchar2(25) not null references lm_users(username), --债务人
booksId number(9) not null references lm_books(id), --被借书的ID号
lentTime date not null default sysdate, --借出时间
ruturnTime date not null default sysdate+30); --应还时间现在我就是想每当添加一条LM_loanbooks记录,与之相关的,被借出的书的借出次数就+1
请问这个怎么去实现?
SQL> ed
Wrote file afiedt.buf 1 --LM_Books表
2 create table LM_Books(
3 id number(9) not null primary key, --书的ID号
4* loanTimes int default 0) --借出次数
SQL> /Table created.SQL> ed
Wrote file afiedt.buf 1 --表LM_loanbooks
2 --用来储存被借出书籍的信息
3 create table LM_loanbooks(
4 id number(10) not null primary key, --id号
5 booksId number(9) not null references lm_books(id) --被借书的ID号
6* )
SQL> /Table created.SQL> ed
Wrote file afiedt.buf 1 create OR REPLACE trigger tri_insert
2 after insert on LM_loanbooks
3 for each row
4 begin
5 UPDATE LM_Books SET loanTimes = loanTimes+1
6 WHERE id = :new.booksId;
7* end;
SQL> /Trigger created.SQL> ed
Wrote file afiedt.buf 1 INSERT INTO LM_Books
2* VALUES(1,0)
SQL> /1 row created.SQL> select * from LM_Books; ID LOANTIMES
---------- ----------
1 0SQL> ed
Wrote file afiedt.buf 1 INSERT INTO LM_Books
2* VALUES(2,0)
SQL> /1 row created.SQL> select * from LM_Books; ID LOANTIMES
---------- ----------
1 0
2 0
SQL> ed
Wrote file afiedt.buf 1 INSERT INTO LM_Loanbooks
2* VALUES(1,1)
SQL> /1 row created.SQL> select * from LM_Books; ID LOANTIMES
---------- ----------
1 1
2 0SQL> ed
Wrote file afiedt.buf 1 INSERT INTO LM_Loanbooks
2* VALUES(2,1)
SQL> /1 row created.SQL> select * from LM_Books; ID LOANTIMES
---------- ----------
1 2
2 0SQL>