mysql update more than one row_一直报[Err] 1172 - Result consisted of more than one row错误

该楼层疑似违规已被系统折叠 隐藏此楼查看此楼

create table BorrowState

(

bcardno varchar(8) not null,

bno varchar(15) primary key,

bdate date ,

rdate date

);

这是之前插入的，想要插入新的就报下面的错误了。

还有3段触发器的编码，全创建成功了，我觉得应该没错，但就是无法测试成功。

6)为读者信息表创建一个删除触发器，当一个读者调出本单位时，将此读者的资料从读者信息表中删除。注意实施业务规则：有借阅书的读者不得从读者信息表中删除。

create trigger reader_del before delete on reader

for each row

begin

declare a varchar(8);

if not exists(select * from borrowstate WHERE bcardno=a and rdate is NULL) THEN

delete from reader where bcardno=a;

end IF;

end;

7)借阅处理：为借阅表设计INSERT触发器，在读者借阅时更改ISBN类别信息表，且可借数量减1，图书信息表是否可借列的值变为“不可借”，读者信息表中该读者已借阅数加1。

create trigger borrow_insert after insert on BorrowState

for each row

begin

declare b varchar(15);

select bno into b from BorrowState;

update ISBNbook,bookstate set brest=brest-1 where BookState.bno=b and ISBNbook.isbnno=BookState.isbnno;

update BookState set bstate="不可借" where bno=b;

update Reader,borrowstate set ramount=ramount-1,bamount=bamount+1 where BorrowState.bno=b and Reader.bcardno=BorrowState.bcardno;

end;

8)还书处理：为借阅信息表设计UPDATE触发器，在读者借阅时更改ISBN类别信息表，且可借数量加1，图书信息表是否可借列的值变为“可借”，读者信息表中该读者已借阅数减1。

create trigger borrow_update after update on BorrowState

for each row

begin

declare b varchar(15);

select bno INTO b from BorrowState where rdate is not NULL;

update ISBNbook,bookstate

set brest=brest+1 where BookState.bno=b and ISBNbook.isbnno=BookState.isbnno;

update BookState

set bstate="可借" where bno=b;

update Reader,borrowstate

set ramount=ramount+1,bamount=bamount-1 where BorrowState.bno=b and Reader.bcardno=BorrowState.bcardno;

end;

下面是建好的其他3个表

create table ISBNbook

(

isbnno varchar(10) primary key,

kind varchar(10),

bname varchar(20),

writer varchar(20),

press varchar(20),

pressdate varchar (8),

price double,

store int(5) not null,

brest int(5) not null,

introduction varchar(200)

)

create table BookState

(

isbnno varchar(10) not null,

bno varchar(15) primary key,

bstate varchar(10) not null

)

create table Reader

(

bcardno varchar(8) primary key,

rname varchar(20),

sex varchar(2),

birth varchar (8),

id varchar(20),

career varchar(20),

ramount int(5) not null,

bamount int(5) not null,

department varchar(20),

address varchar(50),

telephone varchar(20)

)

请各位大神帮忙，万分感谢！