-- 查询验证触发器效果
---------- 查询验证 ----------

-- 1. 书籍可借数量和学生当前借阅量 (应由 trg_sync_book_student 更新):
SELECT book_id, title, total_copies, available_copies FROM books WHERE book_id <= 7;
SELECT student_id, name, max_borrow, current_borrow, account_status FROM students WHERE student_id <= 5;

-- 2. 孙七 (S2023005) 的罚款记录 (应有 trg_calc_fine 生成的逾期罚款 和 手动添加的罚款):
SELECT f.*, s.name as student_name
FROM fines f JOIN students s ON f.student_id = s.student_id
WHERE s.stu_no = 'S2023005';

-- 3. 孙七 (S2023005) 的账户状态 (应由 trg_freeze_account 根据总欠款更新):
SELECT stu_no, name, account_status, current_borrow
FROM students
WHERE stu_no = 'S2023005';

-- 4. 查看热门图书视图:
SELECT * FROM v_hot_books;

-- 5. 查看院系借阅统计视图:
SELECT * FROM v_dept_borrow_stats;

-- 6. 查看逾期详情视图:
SELECT od.*, b.title, s.name as student_name
FROM v_overdue_details od
JOIN books b ON od.book_id = b.book_id
JOIN students s ON od.student_id = s.student_id;

-- 测试数据插入完毕。