亚洲国产日韩欧美一区二区三区,精品亚洲国产成人av在线,国产99视频精品免视看7,99国产精品久久久久久久成人热,欧美日韩亚洲国产综合乱

Using mysql, book management, the problem involves two tables, books and lender

Here comes the question:

Create a trigger to realize the automatic increment of the borrowed books/decrease of the returned books, but the trigger I wrote cannot realize this function, and the final amount has not changed (some amounts in the table were entered manually by me) )

When books are borrowed and the islend attribute is updated, the amount of borrowed books is updated in the lender table at the same time.
After the islend attribute is updated, what is saved is the id of the borrower, the lender. The id in the lender is the primary key, and the islend in the books table is Foreign key, connects these two tables.

The two database table structures are given below. The triggers currently written are as follows:

CREATE TRIGGER returnBook
AFTER UPDATE ON books
FOR EACH ROW
BEGIN
UPDATE lender
SET amount = lender.amount - 1
WHERE id = new.isLend;
END

The render table structure is as follows:

The books table structure is as follows:

Test statement
String sql = "UPDATE books SET isLend=?,date=NULL WHERE bookId=? AND isLend=?";

    try {
        ps = conn.prepareStatement(sql);
        ps.setString(1, "1");
        ps.setString(2, String.valueOf(bookId));
        ps.setString(3, String.valueOf(id));
        row = ps.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }

The borrowing/returning function can be implemented normally, but the trigger function is not implemented