MySQL触发器:在删除表之前先插入日志 [英] MySQL Triggers: INSERT ON log BEFORE DELETE ON table

问题描述

这是我在这里的第一篇文章！=]

this is my first post here! =]

我正在创建一个触发器，每次有人尝试从表中删除某些内容时，都会在日志表上插入

I'm creating a trigger to insert on a log table every time someone tries to delete something from a table

CREATE DEFINER=`root`@`localhost` TRIGGER `user_before_delete`      
BEFORE DELETE ON `user` FOR EACH ROW BEGIN

INSERT INTO log_alert(id, table_id, name)
VALUES('99002', OLD.id, OLD.name);

SIGNAL SQLSTATE '99002'
SET MESSAGE_TEXT = 'TRYING TO REMOVE A USER!'
END

但是问题是:SIGNAL SQLSTATE将回滚整个操作！那么，如何插入LOG TABLE并防止用户删除?

but the problem is: the SIGNAL SQLSTATE rollback the whole operation! So, how do I INSERT on the LOG TABLE and PREVENTE from DELETING the user?

推荐答案

Marc B解决方法对我有用=]

Marc B workaround worked for me =]

quick/dirty workaround: change log_alert to be a myisam table. no transactions there, so no way to roll back your insert. –  Marc B 15 hours ago

只需将 log_alert 更改为MyISAM类型，INSERTS就不会回滚

Just changing the log_alert to MyISAM type and the INSERTS wont rollback anymore

这篇关于MySQL触发器:在删除表之前先插入日志的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！