CODEWITHSUNDEEP
phpsqloracle-databaseplsqltriggers
B-Ray
B-Ray

Reputation: 471

Using a Trigger in Oracle to Log changes to A Table

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;

Any help would be greatly appreciated!

Upvotes: 3

Views: 5536

Answers (1)

steve
steve

Reputation: 6020

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;

Upvotes: 1

Related Questions