当前位置：文江博客话题详情

我需要为删除操作创建触发器，以备份审计表中的所有字段旧值

发布于 2024-08-27 09:41:50 字数 698 浏览 4 评论 0原文

我需要为删除操作创建触发器，以备份审计表中的所有字段旧值。

我有一个审计表的表结构

id、menuid、字段、旧值、改变了

，只要从具有 21 个字段的母表（菜单）中删除任何行，每个字段的旧值都应该使用新的审核 id 插入到审核表中。

就像如果我删除具有以下字段的行：

菜单ID、姓名、年龄、地址、性别、城镇

现在位于审核表中，应为上面给出的每个字段单独插入 6 行，如下所示：

AUdit 表：

id=2(audittable id) 
             menuid = menuid
             field = name
             oldvalue = joy
             changedone = (whatever the deleted time was)

             id=3(audittable id) 
             menuid = menuid
             field = age
             oldvalue = 23
             changedone = (whatever the deleted time was)

等等..

原文

I need to create Trigger for delete action which backsup all fields old value in audit table.

I have a table structure for audit table as

id, menuid, field, oldvalue,
changedone

now whenever any of the row is deleted from its mother table(menu) having 21 fields in count, every fields old value should get insert in the audit table with new audit id..

like If I delete a row having fields as:

menuid, name, age, address, sex, town

now in audit table 6 rows should get inserted seperately for every field given above as:

AUdit Table:

id=2(audittable id) 
             menuid = menuid
             field = name
             oldvalue = joy
             changedone = (whatever the deleted time was)

             id=3(audittable id) 
             menuid = menuid
             field = age
             oldvalue = 23
             changedone = (whatever the deleted time was)

an so on..

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

天邊彩虹 2024-09-03 09:41:50

像这样的东西应该就是您正在寻找的。

delimiter //
CREATE TRIGGER audit_menu BEFORE DELETE ON menu
FOR EACH ROW
BEGIN
    INSERT INTO audit (menuid, field, oldvalue, changedone) VALUES
        (OLD.menuid, 'name', OLD.name, UNIX_TIMESTAMP() ),
        (OLD.menuid, 'age', OLD.age, UNIX_TIMESTAMP() ),
        (OLD.menuid, 'address', OLD.address, UNIX_TIMESTAMP() ),
        (OLD.menuid, 'sex', OLD.sex, UNIX_TIMESTAMP() ),
        (OLD.menuid, 'town', OLD.town, UNIX_TIMESTAMP() )
END;//
delimiter ;

Something like this should be what you're looking for.

delimiter //
CREATE TRIGGER audit_menu BEFORE DELETE ON menu
FOR EACH ROW
BEGIN
    INSERT INTO audit (menuid, field, oldvalue, changedone) VALUES
        (OLD.menuid, 'name', OLD.name, UNIX_TIMESTAMP() ),
        (OLD.menuid, 'age', OLD.age, UNIX_TIMESTAMP() ),
        (OLD.menuid, 'address', OLD.address, UNIX_TIMESTAMP() ),
        (OLD.menuid, 'sex', OLD.sex, UNIX_TIMESTAMP() ),
        (OLD.menuid, 'town', OLD.town, UNIX_TIMESTAMP() )
END;//
delimiter ;

回复收藏 0 原文

~没有更多了~