在Oracle中使用触发器记录对表的更改 [英] Using a Trigger in Oracle to Log changes to A Table

问题描述

我为一个班级准备了一个项目.当我们的两个表对它们进行了更改时，我们需要创建一个日志:insert/update/delete.我们需要使用Oracle触发器和PL-SQL.在日志文件中，我们需要记录UserID，DateTime，IPAddress和Event(插入/更新/删除).我知道如何设置触发器，但是我遇到的主要问题是UserID(从以UserID在Users表中登录到PHP站点)，更重要的是IPAddress.这是我到目前为止所拥有的.

I have a project for one of my classes. We need to create a log when two of our tables have changes made to them: insert/update/delete. We are required to use Oracle Triggers and PL-SQL. In the log file we need to record the UserID, DateTime, IPAddress, and Event (insert/update/delete). I know how to set up the trigger, but the main problems I have is with the UserID (from logging into a PHP site with the UserID being in a Users table) and more importantly IPAddress. Here is what I have so far.

CREATE OR REPLACE TRIGGER tr_movie_ai
AFTER INSERT OR UPDATE OR DELETE
ON Movies
FOR EACH ROW
DECLARE
    v_username VARCHAR(20);
    v_ipaddress VARCHAR(13);
    v_date NUMBER := FLOOR(SYSDATE);
BEGIN
    SELECT User INTO v_username FROM dual;
    SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') INTO v_ipaddress FROM dual;
    INSERT INTO Logs (USERID, DATETIME, IPADDRESS, EVENT, DESCRIPTION) VALUES (user, v_date, v_ipaddress, 'Movie Created', 'Movie created'));
END;

任何帮助将不胜感激！

推荐答案

有两个伪列:uid和user，您可以在values子句中使用它们，并且以下sql返回客户端的ip地址:

There are two pseudocolumns: uid and user, you can use them in your values clause and the following sql returns the ip address of the client:

SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM dual;

这篇关于在Oracle中使用触发器记录对表的更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！