创建另外一张表来存放触发器动作数据。
| mysql> create table `ttlsa_users3` ( -> `uid` int(11) unsigned, -> `userinfo` varchar(200), -> ); mysql> create table `ttlsa_users3` ( -> `uid` int(11) unsigned, -> `userinfo` varchar(200), -> ); |
2.2 创建触发器
| mysql> delimiter // mysql> create trigger ttlsa_users_ai -> after insert on ttlsa_users -> for each row -> insert into ttlsa_users3 (uid, userinfo) values(uid, json_object(NEW.uid, NEW.username, NEW.password)); -> // mysql> create trigger ttlsa_users_au -> after update on ttlsa_users -> for each row -> update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid; -> // mysql> delimiter // mysql> create trigger ttlsa_users_ai -> after insert on ttlsa_users -> for each row -> insert into ttlsa_users3 (uid, userinfo) values(uid, json_object(NEW.uid, NEW.username, NEW.password)); -> // mysql> create trigger ttlsa_users_au -> after update on ttlsa_users -> for each row -> update ttlsa_users3 set userinfo=json_object(NEW.uid, NEW.username, NEW.password) where uid=OLD.uid; -> // |
2.3 测试
| mysql> insert into ttlsa_users values (890,'xuhh',md5('abc'),NULL,'test trigger')// |
| Query OK, 1 row affected (0.01 sec) |
| mysql> select * from ttlsa_users// |
| +-----+-------------+----------------------------------+---------------------+------------------------------------+ | uid | username | password | createtime | json_data | +-----+-------------+----------------------------------+---------------------+------------------------------------+ | 888 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 11:27:01 | website - "http://www.ttlsa.com" | | 889 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 14:08:44 | xuhh | | 890 | xuhh | 900150983cd24fb0d6963f7d28e17f72 | 2013-08-14 16:40:49 | test trigger | +-----+-------------+----------------------------------+---------------------+------------------------------------+ 3 rows in set (0.00 sec) |
| mysql> select * from ttlsa_users3// |
| +-----------------------------------------------------------------------------+------+ | userinfo | uid | +-----------------------------------------------------------------------------+------+ | {"uid":890,"username":"xuhh","password":"900150983cd24fb0d6963f7d28e17f72"} | 890 | +-----------------------------------------------------------------------------+------+ 2 rows in set (0.00 sec) |
