mysql 触发器使用demo

mysql 触发器使用demo

CREATE TRIGGER cc // 触发器名称
BEFORE INSERT ON a //当a表有插入
FOR EACH ROW
BEGIN
	INSERT INTO b (`name`)
	VALUES('当a新增时 b name插入的值');  //触发b表插入 name字段赋values值
END

INSERT INTO a (dd) VALUES ('测试 BEFORE INSERT 触发器');

SHOW TRIGGERS //查看所有触发器

SHOW CREATE TRIGGER cc  //查看cc触发器

DROP TRIGGER IF EXISTS cc //删除cc触发器

//创建a 表

create table a (id int primary key auto_increment,name varchar(20),sex enum("male","female"),age int);
//创建b 表
create table b (l_id int primary key auto_increment,l_user varchar(20),l_action enum("insert","update","delete"),l_time datetime);
//创建c 表
CREATE TABLE `c`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `log` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `ctiam` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;



//创建a - b 新增数据触发器
create trigger a1 after insert on a for each row begin insert into b value(null,user(),"insert",now()); end
//创建a - b 修改数据触发器
create trigger a2 after update on a for each row begin insert into b value(null,user(),"update",now()); end
//创建a - b 删除数据触发器
create trigger a3 after delete on a for each row begin insert into b value(null,user(),"delete",now()); end


//创建a - c 新增数据触发器
create trigger c1 after insert on a for each row begin insert into c value(null,CONCAT('添加','姓名',new.`name`,'姓别',new.sex,'年龄',new.age),now()); end


//创建a - c  修改数据触发器
create trigger c2 after update on a for each row begin insert into c value(null,CONCAT('修改','新姓名',new.`name`,'老姓名',old.`name`,'姓别',new.sex,'年龄',new.age),now()); end

//创建a - c  修改数据触发器
create trigger c3 after delete on a for each row begin insert into c value(null,CONCAT('删除','姓名',old.`name`,'姓别',old.sex,'年龄',old.age),now()); end