MySQL事务及触发器

目录(?)[+]

事务

提交或回滚
常见的事务指令
事务的特点ACID

触发器

创建触发器
管理触发器

事务

create table swpu(id int primary key auto_increment,money decimal(10,2) comment '学费');insert into swpu values(null,5000);create table swpu_stu(id int primary key auto_increment,stu_money decimal(10,2) comment '学生财产');insert into swpu_stu values(null,7000);select * from swpu;select * from swpu_stu;

例如，学生交杂物费100元给学校

update swpu_stu set stu_money=stu_money-100 where id=1;update swpu set money=money+100 where id=1;select * from swpu;select * from swpu_stu;

事务：如果整组成功，意味着全部sql都实现。如果其中任何一个失败，意味着整个操作都失败。失败，意味着整个过程都是没有意义的。应该使数据库回到操作前的初始状态。

如何处理？ 1、失败后，可以回到开始位置。 2、没都成功之前，别的用户（进程，会话）是不能看到操作内的数据修改的。

具体思路： 就是在一组操作之前，设计一个记号（备份点）。如果执行成功，让别人看到数据的修改。如果执行失败，别人不能看到，应该回到记号位置。

实现： 使用innodb存储引擎的事务日志功能。如果成功，则将结果提交到数据库内。意味着就更改了数据库内容。

SQL的执行的2个阶段： 1、执行阶段 2、将执行结果，提交到数据库阶段，其中我们的事务日志就是保存执行阶段的结果。如果用于选择提交，则才将执行结果提交到数据库中。

默认的执行方式叫自动提交。执行完毕，自动完成提交工作。因此需要关闭自动提交功能。

存在一个系统的变量autocommit，可以对自动提交进行配置。

set autocommit=0;show variables like 'autocommit';

关闭后再次执行相应的更新语句，发现在其他的连接中查看数据时，没有发生变化，因为结果没有提交。

提交或回滚

在此基础上，执行完所有的sql语句。判断是否成功（出现错误，包括语法错误和逻辑错误，服务器错误）。成功：将结果提交。利用commit。失败：回到开始位置。利用rollback。

update swpu_stu set stu_money=stu_money-100 where id=1;update swpu set money=money+100 where id=1;select * from swpu;select * from swpu_stu;commit;select * from swpu;select * from swpu_stu;

提交之前：

提交之后：

需要手动改为自动提交。

set autocommit=1;

常见的事务指令

开启事务 start transcation; 关闭自动提交，如果事务结束了（成功或失败），都会回到自动提交机制，回到start时的状态。成功：commit; 失败：rollback;

start transcation;update swpu_stu set stu_money=stu_money-100 where id=1;update swpu set money=money+100 where id=1;select * from swpu;select * from swpu_stu;commit;select * from swpu;select * from swpu_stu;

事务限定：在innodb(DBD)存储引擎下生效。

注意：要求start transcation;开启事务功能。而不用set autocommit=0;开启事务功能。

事务的特点ACID：

1、原子性 2、一致性（从开始到结束，数据不会受到事务之外其他语句的改变。） 3、隔离性（一个事务的操作不会影响另一个事务） 4、持久性（修改永久生效）

触发器

触发器：监听数据进行操作。在当前表上，设置一个对每行数据的一个**，监听相关事件。每当事件发生时，会执行一段由sql完成的一段功能代码。

触发器的元素：事件，执行代码。

创建触发器

create trigger 名字事件执行性代码

事件： 插入 insert 删除 delete 修改 update

时间的时机：执行之前和执行之后（after before）。由时机和事件在一起形成了六种事件。

before insert，before delete，before updateafter insert，after delete，after update

事件规定：在那个表上的什么时机的什么动作上。

可执行代码： SQL语句组成的代码。

create trigger name_jiaoxuefei after update on swpu_stu for each rowupdate swpu set money=money+150;select * from swpu;select * from swpu_stu;

触发，触发程序：特定时间发生，即触发。

update swpu_stu set stu_money=stu_money-150 where id=1;

注意： 触发器不能同名。目前mysql只支持一类事件，设置一个触发器。

create trigger name_jiaoxuefei2 after update on swpu_stu for each rowupdate swpu set money=money-150;

管理触发器：

删除：drop

drop trigger trigger_name;drop trigger name_jiaoxuefei;

查看：show

show create trigger trigger_name;show create trigger name_jiaoxuefei;

在触发器内，获得触发给触发程序时的数据。利用触发程序内的new和old来完成。

Old：监听事件所在表上的数据，在事件发生之前时的数据，旧的数据。

New：监听表上，事件发生之后，新处理完毕的数据。数据就是触发该事件的记录。

事件update可以使用new和old

update swpu_stu set stu_money=stu_money-150 where id=1;//1000 950(old.stu_money-new.stu_money)drop trigger name_jiaoxuefei;create trigger name_jiaoxuefei after update on swpu_stu for each rowupdate swpu set money=money+(old.stu_money-new.stu_money);update swpu_stu set stu_money=stu_money-1500 where id=1;

事件是insert呢？不能使用old alter table swpu add stu_count int default 0;

create trigger name_kaixue after insert on swpu_stufor each rowupdate swpu set stu_count = stu_count+1;drop trigger name_kaixue;create trigger name_kaixue after insert on swpu_stufor each rowupdate swpu set stu_count = stu_count+new.id;

事件是delete呢？不能使用new

create trigger name_biye after insert on swpu_stufor each rowdelete swpu set stu_count = stu_count-old.id;

如果一个触发程序由多条sql语句组成。应该满足如下： 1、语句组成语句块（begin end）用来标识语句块。 2、语句块的语句需要独立的语句结束符，分号。

由于触发器程序内使用分号作为语句的结束符，那么当命令行客户端碰到分号时，就应该理解成触发程序内的子语句结束，而不是整个创建触发器的语句结束。因此，应该通过修改命令行的语句结束符达到目的。 delimiter语句可以完成设置语句结束符。

别忘了修改完之后，再改回去。

drop trigger name_kaixue;delimiter $$create trigger name_kaixue after insert on swpu_stufor each rowbeginupdate swpu set stu_count = stu_count+1;update swpu set money = money+100;end$$delimiter ;insert into swpu_stu values(2,2000);select * from swpu;