mysql触发器

1.创建数据库my

mysql>create database my;

2.打开数据库my

mysql>use my

3. 在数据库my中创建数据表depts

mysql>create table depts(id int(11),name varchar(20),description varchar(255));

4.在数据库表depts中插入记录

mysql>insert into depts values(1,'人事部',null),(2,'研发部',null),(3,'市场部',null),(4,'培训部',null);

5.创建一个储存数据的表test

mysql>create table test(user_id varchar(20),description varchar(50));

实例 一：

6.创建触发器

mysql>delimiter $$

mysql>create trigger mytrigger before delete on depts for each row  begin insert into test(user_id,description) values(user(),'depts delete');

mysql> end;

msyql>$$

7.删除记录

mysql>delimiter ;  //以分号结束语句

mysql>delete from depts where name='人事部';

8.查看返回数据表test的数据

mysql>select * from test; 

实例 二

9.触发器二

mysql>delimiter $$

mysql>create trigger mytrigger2 after update on depts for each row

mysql>begin

mysql>insert into test(user_id,description) values(user(),'depts update');

mysql>end;

mysql>$$

10.更新记录

mysql>delimiter ;

mysql>update depts set id=8 where name='研发部';

mysql>select * from test;

11.

触发器实例三：

mysql>delimiter $$

mysql>create trigger mytrigger3 before insert on depts for each row begin

msyql>insert into test(user_id,description) values(user(),'depts insert');

mysql>end;

msyql>$$

12.插入记录

mysql>delimiter ;

mysql>insert into depts values(7,'资源部',null);

13.查看返回表记录

mysql>select * from test;