Mysql trigger

Mysql trigger

作者:​​三十而立​​

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)

如果没有那句“三十而立”，三十岁的男人正可以轻轻松松

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。