MySQL 5.1 中需要一个抽象触发器来更新审核日志
我需要一种方法来检查表中已更改的任何条目并将其传递到审核日志中。它需要从表结构中抽象出来。
例如:
CREATE TRIGGER table1_update
BEFORE UPDATE ON table1
FOR EACH ROW BEGIN
DECLARE i_column_name varchar(32);
DECLARE done INT;
DECLARE cursor1 CURSOR FOR SELECT column_name FROM information_schema.columns WHERE table_name = 'table1';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cursor1;
REPEAT
FETCH cursor1 INTO i_column_name;
IF NOT done THEN
--pass the variable column_name and its old.i_column_name and new.i_column_name values to the audit table
END IF;
UNTIL done END REPEAT;
CLOSE cursor1;
END$$
我们有太多的表需要审核以自定义构建每个 INSERT、UPDATE 和 DELETE 触发器。我尝试了很多事情,但我想我运气不好。有人有什么想法吗?
I need a way to check for and pass entries into an audit log for any entries in a table that have been changed. It needs to be abstracted away from the table structure.
For example:
CREATE TRIGGER table1_update
BEFORE UPDATE ON table1
FOR EACH ROW BEGIN
DECLARE i_column_name varchar(32);
DECLARE done INT;
DECLARE cursor1 CURSOR FOR SELECT column_name FROM information_schema.columns WHERE table_name = 'table1';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cursor1;
REPEAT
FETCH cursor1 INTO i_column_name;
IF NOT done THEN
--pass the variable column_name and its old.i_column_name and new.i_column_name values to the audit table
END IF;
UNTIL done END REPEAT;
CLOSE cursor1;
END$
We have too many tables that need to be audited to custom build every single INSERT, UPDATE, and DELETE trigger. I've tried a number of things and I'm thinking I'm out of luck. Anyone have any ideas?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
您不能拥有抽象触发器,它必须在特定表上定义。最接近的方法是将触发器的代码放入存储过程中,然后每个表的触发器将只调用该过程。
您应该能够轻松地编写一些脚本,使用 information_schema 或类似的内容为所有表创建触发器。
You can't have an abstract trigger, it must be defined on a specific table. The closest you can get is to put the code for the trigger into a stored procedure, and then the triggers for each table will just call the procedure.
You should be able to easily script up something that will create the triggers for all of your tables using the information_schema or something along those lines.