mysql 触发器的详细用法

触发器：

一类特殊的数据库程序，可以监视某种数据的操作(insert/update/delete),并触发相关的操作(insert/update/delete)，保护数据的完整性

应用场景：数据同步 例如：当一个用户完整信息保存在A/B/C三张表中，传统方法维护用户信息需要将对应的信息使用条SQL语句，依次插入，但是使用触发器的方式的话，我们可以只用插入A表中一条数据，就会自动触发数据插入B/C表的操作，这样就可以通过一个表的操作，同步其他操作，实现自动化维护数据的目的。

触发器语法格式:

creat trigger 触发器名称
after/before(触发器工作的时机)
update/delete/insert(触发器监听事件)
on 表名(触发器监听的目标表)
for each row(行级监视，mysql固定写法，oracle不同)
begin
  sql语句集........（触发器执行动作，分号结尾）
end;

删除触发器：drop trigger if exist 触发器名称 查询数据库触发器：show triggers;

行变量：当目标表发生改变时候，变化的行可用行变量表示

new:代表目标表目标行发生改变之后的行 old:代表目标表目标行发生改变之前的行

触发器案例：商品表/订单表(商品数量控制)

触发器监听：insert

create trigger tr1
after insert on orders
for each row
begin
update goods set godnum = godnum-new.godnum where id = new.id;
end;

分析：orders表添加新的记录之后，goods表对应的商品数量同步减少对应的商品订单出数量

触发器监听：delete

create trigger tr2
after delete on orders
for each row
begin
update goods set godnum= godnum+old.godnum 
where id = old.id;
end

分析：客户删除订单，恢复商品表对应商品的数量

触发器监听：update

create trigger tr3
after update on orders
for each row
begin
update goods set godnum= godnum+(old.godnum-new.godnum) 
where id = old.id;
end

分析：客户修改订单，商品表对应商品数量同步更新

触发器声明变量用法：

create trigger tr4
before insert on orders
for each row
begin
    declare godSum int default 0;  -- 定义触发器变量
    select godNum  into godSum from goods where godId = new.godId; -- 变量赋值
    if godSum< new.godNum  then-- 触发器控制逻辑
    update goods set godNum = new.godNum where godId = new.godId;
    end if;
end

分析：当客户新建订单超过商品表的商品数量则修改商品表，保商品表商品库存量非负

mysql触发器特别注意：

①：for each row:必须填写，保证mysql支持行级控制，oracle同时支持行级控制和语句级控制 ②：注意：如果在BEFORE或AFTER触发程序的执行过程中出现错误，将导致调用触发程序的整个语句的失败。对于事务性表，如果触发程序失败（以及由此导致的整个语句的失败），该语句所执行的所有更改将回滚。对于非事务性表，不能执行这类回滚，因而，即使语句失败，失败之前所作的任何更改依然有效。

项目中用到的触发器

DELIMITER $$

USE `ifpm`$$

DROP TRIGGER /*!50032 IF EXISTS */ `addCrmCustAttn`$$

CREATE
    /*!50017 DEFINER = 'ifdp'@'%' */
    TRIGGER `addCrmCustAttn` BEFORE INSERT ON `CRM_CUST_ATTN` 
    FOR EACH ROW 
BEGIN
    DECLARE attnNo VARCHAR(10);
    SELECT ATTN_NO INTO attnNo FROM CRM_CUST_ATTN WHERE UID = NEW.UID AND COOCUST_NO=new.COOCUST_NO ORDER BY ATTN_NO DESC LIMIT 1;
    IF attnNo IS NULL THEN 
    SET attnNo = '0000';
    END IF ;
    SET new.ATTN_NO =  LPAD(attnNo + 1,4,'0');
END;
$$

DELIMITER ;