有没有办法确定是由Web应用程序（php进程）执行数据库行更新，还是通过DB GUI手动运行

I need to allow edits made via a DB GUI though to my auditing system so that they are also being tracked. I am updating the audit table using a BEFORE trigger, and i would like to be able to alter the audit row to indicate what the source of the update was (via an existing 'modified_by' column).

我需要允许通过DB GUI进行编辑,直到我的审计系统,以便它们也被跟踪。我正在使用BEFORE触发器更新审计表,我希望能够更改审计行以指示更新源是什么(通过现有的'modified_by'列)。

I cannot add an additional column to the audit table for this because I want to be able to copy from the updated table to the audit table without specifying table columns like below (I don't want this trigger to require that I update it with changes to the schema).

我无法为审计表添加额外的列,因为我希望能够从更新的表复制到审计表而不指定下面的表列(我不希望此触发器要求我更新它到架构)。

MY TRIGGER:

BEGIN
    INSERT INTO contacts_audit SELECT * FROM contacts WHERE id = NEW.id;
END

PSEUDO TRIGGER:

BEGIN
    IF(GUI EDIT) THEN
        SET NEW.modified_by_id = 1; /* 1 is the administrator id */ 
    END IF;
    INSERT INTO contacts_audit SELECT * FROM contacts WHERE id = NEW.id; 
END

1 个解决方案

#1

A "DB GUI" connecting to the database is a client, a php connection is a client too, the same as any other way to connect to the database, so there is no flag like "this is php".

连接到数据库的“DB GUI”是客户端,php连接也是客户端,与连接数据库的任何其他方式相同,因此没有像“this is php”这样的标志。

You can however use the USER() or SESSION_USER()-function in the trigger to get the active user/hostname of the current connection, so just use a different user/host for php and the gui if you want to seperate them.

但是,您可以在触发器中使用USER()或SESSION_USER() - 函数来获取当前连接的活动用户/主机名,因此如果要分离它们,只需使用不同的用户/主机用于php和gui。

1 个解决方案

#1

更多相关文章

随机推荐