MySql--触发器

MySql中可以使用触发器来得到数据库中数据的变动日志，是管理数据的有力工具。触发器不用直接调用，而是对表的不同操作来触发不同的触发器，现将之总结一下。

/*************************触发器******************************************/	
一、创建触发器
	1、BEFORE INSERT型触发器
		mysql> delimiter ##
		mysql> create table hjf.logtab
			-> (id int not null auto_increment primary key,
			->	oname varchar(20), 
			->	otime varchar(30))##
		Query OK, 0 rows affected (0.01 sec)

		mysql> create trigger hjf.fstin 
			->    before insert ON newInfo
			->    for each row
			->    begin
			->    insert into logtab(oname, otime) value('hjf', SYSDATE());
			->    end##
		Query OK, 0 rows affected (0.43 sec)

		mysql> insert into hjf.newInfo values(12, 'Naic', 23.12, 2, 3)##
		Query OK, 1 row affected (0.03 sec)

		mysql> select * from hjf.logtab##
		+----+-------+---------------------+
		| id | oname | otime               |
		+----+-------+---------------------+
		|  1 | hjf   | 2017-04-08 20:18:01 |
		+----+-------+---------------------+
		1 row in set (0.00 sec)

		mysql> select * from newInfo where id = 12##
		+----+------+-------+-----------+-----------+
		| id | name | score | subjectid | teacherid |
		+----+------+-------+-----------+-----------+
		| 12 | Naic | 23.12 |         2 |         3 |
		+----+------+-------+-----------+-----------+
		1 row in set (0.00 sec)

	2、AFTER INSERT型触发器
	mysql> create trigger hjf.secinsertrg 
		-> 	  after insert ON newInfo  
		-> 	  for each row    
		-> 	  begin    
		-> 	  insert into logtab(oname, otime) value('hjf_after', SYSDATE());    
		->    end## 
	Query OK, 0 rows affected (0.01 sec)

	mysql> insert into hjf.newInfo values(14, 'Maic', 23.12, 2, 3)##
	Query OK, 1 row affected (0.00 sec)

	mysql> select * from logtab##
	+----+-----------+---------------------+
	| id | oname     | otime               |
	+----+-----------+---------------------+
	|  1 | hjf       | 2017-04-08 20:18:01 |
	|  2 | hjf       | 2017-04-08 20:27:32 |
	|  3 | hjf_after | 2017-04-08 20:27:32 |
	+----+-----------+---------------------+
	3 rows in set (0.00 sec)

	mysql> select * from newInfo where id = 14##
	+----+------+-------+-----------+-----------+
	| id | name | score | subjectid | teacherid |
	+----+------+-------+-----------+-----------+
	| 14 | Maic | 23.12 |         2 |         3 |
	+----+------+-------+-----------+-----------+
	1 row in set (0.00 sec)

二、删除触发器
	mysql> drop trigger hjf.secinsertrg## 
	Query OK, 0 rows affected (0.00 sec)

三、修改触发器
	mysql> drop trigger hjf.secinsertrg##  
	Query OK, 0 rows affected (0.00 sec)
	
	mysql> create trigger hjf.secinsertrg 
		->    before delete ON newInfo  
		->    for each row   
		->    begin    
		->    insert into logtab(oname, otime) value('hjf_after', SYSDATE());    
		->    end##
	Query OK, 0 rows affected (0.01 sec)

/*****************************************************************************/