Mysql 触发器

 

 Mysql实现插入，修改，删除触发器，在日志表记录数据

　

DELIMITER $$

USE `hb`$$

DROP FUNCTION IF EXISTS `insert_log`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `insert_log`(op_action INT, rowid VARCHAR(32), table_name VARCHAR(100)) RETURNS VARCHAR(32) CHARSET utf8
BEGIN  
     SET @id = REPLACE(UUID(), '-', '');
     SET @nowtime = NOW();
     INSERT INTO log_update_message(dmg_id, dmg_action, dmg_table_name, dmg_rowid, dmg_create_time, dmg_state, dmg_update_time, dmg_update_count)
     VALUES(@id, op_action, table_name, rowid, @nowtime, 0, @nowtime, 0);
     RETURN @id;
END$$

DELIMITER ;

删除操作

DELIMITER $$

USE `hb`$$

DROP TRIGGER /*!50032 IF EXISTS */ `asm_user_delete`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `asm_user_delete` AFTER DELETE ON `asm_user` 
    FOR EACH ROW BEGIN
     SET @table_name = 'asm_user';
     SET @rowid = old.usr_id;
     SET @action = 2;
     SET @id = hb.`insert_log`(@action, @rowid, @table_name);
END;
$$

DELIMITER ;

插入操作

DELIMITER $$

USE `hb`$$

DROP TRIGGER /*!50032 IF EXISTS */ `asm_user_insert`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `asm_user_insert` AFTER INSERT ON `asm_user` 
    FOR EACH ROW BEGIN
     SET @table_name = 'asm_user';
     SET @rowid = new.usr_id;
     SET @action = 0;
     SET @id = hb.`insert_log`(@action, @rowid, @table_name);
END;
$$

DELIMITER ;

修改操作

DELIMITER $$

USE `hb`$$

DROP TRIGGER /*!50032 IF EXISTS */ `asm_user_update`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `asm_user_update` AFTER UPDATE ON `asm_user` 
    FOR EACH ROW BEGIN
     SET @table_name = 'asm_user';
     SET @rowid = old.usr_id;
     SET @action = 1;
     SET @id = hb.`insert_log`(@action, @rowid, @table_name);
END;
$$

DELIMITER ;