mysql触发器——修改tableA表的field1字段影响tableB表的field2字段

mysql触发器

有时候一个字段的修改会影响另外一个字段的数据，这时候用java就需要书写较多的代码，如果用mysql自带的触发器将会变得很简单。

案例： 应收明细表的应收金额被修改后，修改应收账单表的确认字段为未确认。

sql实现：

-- 创建一个触发器
create trigger fin_receivable_bill_update_trigger after update on fin_receivable for each row
begin
		-- 定义变量 取自fin_receivable 表里面的字段
        DECLARE related_id bigint;
        -- 判断新旧值是否相等
        if (OLD.un_received_amount != NEW.un_received_amount) then
        	-- 修改应收账单表的确认字段
            update fin_receivable_bill set is_confirm_bill = 1002
                where is_deleted = 0 and id in (
                select bill_id from fin_receivable_bill_item 
                where is_deleted = 0 and receivable_id = related_id
                );
end;

解释：

1. 创建触发器：create trigger 触发器的名字：fin_receivable_bill_update_trigger
2. 目标表执行修改操作之后：after update fin_receivable
3. 判断字段新旧值不一样：if(OLD.un_received_amount != NEW.un_received_amount)
4. 定义变量：DECLARE related_id bigint;
5. 执行具体的业务逻辑：update fin_receivable_bill set is_confirm_bill = 1002 where is_deleted = 0 and id in ( select bill_id from fin_receivable_bill_item where is_deleted = 0 and receivable_id = related_id);