SQL如何根据pengunjung表status_lolos值自动更新log表统计字段累加值

实现方案 #

你这个需求可以通过数据库的**触发器（Trigger）**实现，触发器会在pengunjung表的数据发生变化时自动执行预设逻辑更新log表。

实现逻辑说明 #

• 触发器触发时机选 AFTER INSERT（新增访客记录后）和 AFTER UPDATE（更新访客审核状态后）即可覆盖所有状态变更场景
• 核心规则：
  1. 当status_lolos为true时，log表对应日期的jumlah_pengunjung_masuk字段值+1
  2. 当status_lolos为false时，log表对应日期的jumlah_pengunjung_ditolak字段值+1

示例代码（MySQL环境） #

1. 新增访客记录触发的触发器

DELIMITER //
CREATE TRIGGER trg_pengunjung_after_insert
AFTER INSERT ON pengunjung
FOR EACH ROW
BEGIN
  IF NEW.status_lolos = TRUE THEN
    UPDATE log 
    SET jumlah_pengunjung_masuk = jumlah_pengunjung_masuk + 1
    WHERE tanggal = NEW.tanggal_tercatat;
  ELSE
    UPDATE log 
    SET jumlah_pengunjung_ditolak = jumlah_pengunjung_ditolak + 1
    WHERE tanggal = NEW.tanggal_tercatat;
  END IF;
END //
DELIMITER ;

2. 修改访客审核状态触发的触发器

如果业务允许修改已录入的status_lolos状态，需要额外新增更新触发器，先扣除旧状态对应的统计值再累加新状态的统计值：

DELIMITER //
CREATE TRIGGER trg_pengunjung_after_update
AFTER UPDATE ON pengunjung
FOR EACH ROW
BEGIN
  -- 扣除旧状态对应的统计值
  IF OLD.status_lolos = TRUE THEN
    UPDATE log 
    SET jumlah_pengunjung_masuk = jumlah_pengunjung_masuk - 1
    WHERE tanggal = OLD.tanggal_tercatat;
  ELSE
    UPDATE log 
    SET jumlah_pengunjung_ditolak = jumlah_pengunjung_ditolak - 1
    WHERE tanggal = OLD.tanggal_tercatat;
  END IF;
  -- 累加新状态对应的统计值
  IF NEW.status_lolos = TRUE THEN
    UPDATE log 
    SET jumlah_pengunjung_masuk = jumlah_pengunjung_masuk + 1
    WHERE tanggal = NEW.tanggal_tercatat;
  ELSE
    UPDATE log 
    SET jumlah_pengunjung_ditolak = jumlah_pengunjung_ditolak + 1
    WHERE tanggal = NEW.tanggal_tercatat;
  END IF;
END //
DELIMITER ;

注意事项 #

请确保log表每天都有对应的初始记录（tanggal字段每日记录提前存在，两个统计字段默认值为0），否则UPDATE语句会找不到对应记录不生效。如果需要自动创建不存在的日期记录，可以把UPDATE逻辑替换为INSERT INTO ... ON DUPLICATE KEY UPDATE语法，提前将log表的tanggal字段设为唯一键即可，示例如下：

-- 替换新增场景下的UPDATE逻辑
INSERT INTO log (tanggal, jumlah_pengunjung_masuk, jumlah_pengunjung_ditolak)
VALUES (NEW.tanggal_tercatat, 1, 0)
ON DUPLICATE KEY UPDATE jumlah_pengunjung_masuk = jumlah_pengunjung_masuk + 1;

如果你使用的是PostgreSQL等其他数据库，仅需要调整对应数据库的触发器创建语法，核心逻辑保持不变即可。

内容的提问来源于stack exchange，提问作者Rezafauzan1945