触发器实现两表字段求和更新第三表字段报错求助

原触发器存在以下关键错误：

1. 未定义变量e：触发器中使用了未声明的e，MySQL触发器需用NEW/OLD指代触发行数据，且AFTER INSERT触发器无法直接修改NEW行，需通过UPDATE语句更新目标表。
2. JOIN逻辑错误：INNER JOIN会过滤掉仅存在tb_vendedor或仅存在tb_flexuser的企业，导致这些企业的求和结果为NULL，而非正确的0或单表求和值。
3. 触发场景不完整：仅在插入tb_empresarial时触发，但当tb_vendedor或tb_flexuser的qtdanuncios字段更新、新增或删除时，企业总广告数也需同步更新。

需为tb_vendedor和tb_flexuser分别创建INSERT、UPDATE、DELETE触发器，覆盖所有可能导致总广告数变化的场景，同时处理tb_empresarial插入时的初始值设置。

1. 企业初始化触发器 #

新增企业时，将总广告数初始化为0（此时无关联的销售人员或灵活用户）：

CREATE TRIGGER init_empresarial_qtdanuncios
BEFORE INSERT ON tb_empresarial
FOR EACH ROW
SET NEW.qtdanuncios_empresarial = 0;

2. tb_vendedor数据变化触发器 #

插入tb_vendedor时同步更新 #

CREATE TRIGGER update_empresarial_after_vendor_insert
AFTER INSERT ON tb_vendedor
FOR EACH ROW
UPDATE tb_empresarial
SET qtdanuncios_empresarial = (
    COALESCE((SELECT SUM(qtdanuncios_vendedor) FROM tb_vendedor WHERE code_empresarial = NEW.code_empresarial), 0)
    + COALESCE((SELECT SUM(qtdanuncios_flexuser) FROM tb_flexuser WHERE code_empresarial = NEW.code_empresarial), 0)
)
WHERE id_empresarial = NEW.code_empresarial;

更新tb_vendedor时同步更新 #

CREATE TRIGGER update_empresarial_after_vendor_update
AFTER UPDATE ON tb_vendedor
FOR EACH ROW
UPDATE tb_empresarial
SET qtdanuncios_empresarial = (
    COALESCE((SELECT SUM(qtdanuncios_vendedor) FROM tb_vendedor WHERE code_empresarial = NEW.code_empresarial), 0)
    + COALESCE((SELECT SUM(qtdanuncios_flexuser) FROM tb_flexuser WHERE code_empresarial = NEW.code_empresarial), 0)
)
WHERE id_empresarial = NEW.code_empresarial;

删除tb_vendedor时同步更新 #

CREATE TRIGGER update_empresarial_after_vendor_delete
AFTER DELETE ON tb_vendedor
FOR EACH ROW
UPDATE tb_empresarial
SET qtdanuncios_empresarial = (
    COALESCE((SELECT SUM(qtdanuncios_vendedor) FROM tb_vendedor WHERE code_empresarial = OLD.code_empresarial), 0)
    + COALESCE((SELECT SUM(qtdanuncios_flexuser) FROM tb_flexuser WHERE code_empresarial = OLD.code_empresarial), 0)
)
WHERE id_empresarial = OLD.code_empresarial;

3. tb_flexuser数据变化触发器 #

插入tb_flexuser时同步更新 #

CREATE TRIGGER update_empresarial_after_flexuser_insert
AFTER INSERT ON tb_flexuser
FOR EACH ROW
UPDATE tb_empresarial
SET qtdanuncios_empresarial = (
    COALESCE((SELECT SUM(qtdanuncios_vendedor) FROM tb_vendedor WHERE code_empresarial = NEW.code_empresarial), 0)
    + COALESCE((SELECT SUM(qtdanuncios_flexuser) FROM tb_flexuser WHERE code_empresarial = NEW.code_empresarial), 0)
)
WHERE id_empresarial = NEW.code_empresarial;

更新tb_flexuser时同步更新 #

CREATE TRIGGER update_empresarial_after_flexuser_update
AFTER UPDATE ON tb_flexuser
FOR EACH ROW
UPDATE tb_empresarial
SET qtdanuncios_empresarial = (
    COALESCE((SELECT SUM(qtdanuncios_vendedor) FROM tb_vendedor WHERE code_empresarial = NEW.code_empresarial), 0)
    + COALESCE((SELECT SUM(qtdanuncios_flexuser) FROM tb_flexuser WHERE code_empresarial = NEW.code_empresarial), 0)
)
WHERE id_empresarial = NEW.code_empresarial;

删除tb_flexuser时同步更新 #

CREATE TRIGGER update_empresarial_after_flexuser_delete
AFTER DELETE ON tb_flexuser
FOR EACH ROW
UPDATE tb_empresarial
SET qtdanuncios_empresarial = (
    COALESCE((SELECT SUM(qtdanuncios_vendedor) FROM tb_vendedor WHERE code_empresarial = OLD.code_empresarial), 0)
    + COALESCE((SELECT SUM(qtdanuncios_flexuser) FROM tb_flexuser WHERE code_empresarial = OLD.code_empresarial), 0)
)
WHERE id_empresarial = OLD.code_empresarial;

• COALESCE函数用于处理SUM返回NULL的情况（当某表无对应企业数据时，SUM返回NULL，COALESCE将其转为0）。
• 每个触发器仅更新当前操作对应的企业数据，避免全表扫描，提升效率。
• 覆盖了所有可能影响总广告数的场景：企业初始化、销售人员/灵活用户的增删改操作。

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