Home | Mirror | Search |
history
CREATE TABLE user_history SELECT * FROM user WHERE 1 <> 1 DELIMITER // CREATE TRIGGER user_history BEFORE update ON user FOR EACH ROW BEGIN insert into user_history SELECT * FROM user WHERE id = OLD.id; END; // DELIMITER ;
DROP TRIGGER admin_user_history; DELIMITER // CREATE TRIGGER admin_user_history BEFORE update ON admin_user FOR EACH ROW BEGIN insert into admin_user_history SELECT * FROM admin_user WHERE user_id = OLD.user_id; END; // DELIMITER;
例 5.1. BEFORE/AFTER
DROP TRIGGER MY_TEST_MONITOR; DELIMITER // CREATE TRIGGER MY_TEST_MONITOR BEFORE insert ON MY_TEST FOR EACH ROW BEGIN INSERT INTO MY_TEST_MONITOR SELECT * FROM MY_TEST WHERE TICKET = NEW.TICKET; END; // DELIMITER;
DROP TRIGGER MY_TEST_MONITOR; DELIMITER // CREATE TRIGGER MY_TEST_MONITOR AFTER insert ON MY_TEST FOR EACH ROW BEGIN INSERT INTO MY_TEST_MONITOR SELECT * FROM MY_TEST WHERE TICKET = NEW.TICKET; END; // DELIMITER;
例 5.2. uuid()
delimiter $$ CREATE TABLE `member` ( `uuid` char(36) NOT NULL, `username` varchar(20) DEFAULT NULL, `password` varchar(32) DEFAULT NULL, PRIMARY KEY (`uuid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8$$ CREATE DEFINER=`root`@`%` TRIGGER `test`.`member_before_insert` BEFORE INSERT ON `test`.`member` FOR EACH ROW SET new.uuid = uuid() $$