Mysql trigger

• 作者: 三十而立
• 时间：2009年10月10日 22:13:48
• 本文出自 “inthirties（三十而立）”博客，转载请务必注明作者和保留出处http://blog.csdn.net/inthirties/archive/2009/10/10/4649062.aspx

 

mysql> show create table account G;

 

CREATE TABLE `account` (
  `acc_id` bigint(20) NOT NULL DEFAULT '0',
  `acc_first_name` varchar(100) DEFAULT NULL,
  `acc_last_name` varchar(100) DEFAULT NULL,
  `acc_email` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`acc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

mysql> show create table account_update_log G;

 

 

CREATE TABLE `account_update_log` (
  `id` bigint(20) NOT NULL,
  `oldfirstname` varchar(100) DEFAULT NULL,
  `oldlastname` varchar(100) DEFAULT NULL,
  `oldemail` varchar(100) DEFAULT NULL,
  `newfirstname` varchar(100) DEFAULT NULL,
  `newlastname` varchar(100) DEFAULT NULL,
  `newemail` varchar(100) DEFAULT NULL,
  KEY `IDX_AccountLog_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 

mysql> show create trigger trig_account_update G;

 

*************************** 1. row ***************************
               Trigger: trig_account_update
              sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTI
TUTION
SQL Original Statement: CREATE DEFINER=`poc`@`localhost` trigger trig_account_up
date
after update on account
for each row
begin
      insert into account_update_log values(
      old.acc_id,
      old.acc_first_name, old.acc_last_name, old.acc_email,
      new.acc_first_name, new.acc_last_name, new.acc_email
      );
END;

 

可以看到和Oracle的trigger很相似，提供行级trigger。

 

试试吧

mysql> select count(*) from account_update_log;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.03 sec)

 

mysql> select acc_id, acc_first_name from account where acc_id<=3;
+--------+------------------------+
| acc_id | acc_first_name         |
+--------+------------------------+
|      1 | david1
|      2 | david2                 |
|      3 | david3                 |
+--------+------------------------+

 

mysql> update account  set acc_first_name=concat(acc_first_name, '--updated') wh
ere acc_id<=3;
Query OK, 3 rows affected (0.05 sec)

 

mysql> select acc_id, acc_first_name from account where acc_id<=3;
+--------+-----------------+
| acc_id | acc_first_name  |
+--------+-----------------+
|      1 | david1--updated |
|      2 | david2--updated |
|      3 | david3--updated |
+--------+-----------------+
3 rows in set (0.00 sec)

 

mysql> select id, oldfirstname, newfirstname from account_update_log;
+----+--------------+-----------------+
| id | oldfirstname | newfirstname    |
+----+--------------+-----------------+
|  1 | david1       | david1--updated |
|  2 | david2       | david2--updated |
|  3 | david3       | david3--updated |
+----+--------------+-----------------+
3 rows in set (0.00 sec)

 

• 如果没有那句“三十而立”，三十岁的男人正可以轻轻松松
• 专业论坛 http://www.inthirties.com
• 技术博客 http://blog.csdn.net/inthirties
• 个人站点 http://blog.inthirties.com
• Oracle Mysql技术论坛| 打造实用的Oracle Mysql技术交流园地