java - trigger to update two tables at the same time

Question

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

Answer 1

CREATE TRIGGER lendBook
AFTER UPDATE ON books
FOR EACH ROW
BEGIN
sql语句1;
sql语句2;
END

Answer 2

There is nothing wrong with the trigger itself, except that we need to pay attention to the null problem mentioned by @夜 vanishing in the summer [lingchenxiaoshixiari]

You can add a log-like table to the trigger to see if the trigger has been executed