I have setup a script, created from various links on the web to trace activity in a table using a trigger.
/* Create the MinUser Table */
CREATE TABLE MinUser (
User_Id int NOT NULL PRIMARY KEY,
User_Name varchar (50) NOT NULL,
User_Password varchar (50) NOT NULL,
User_Email varchar (50),
User_Role varchar (50),
UNIQUE (User_Name)
) WITH (OIDS=FALSE);
/* Create the Audit Table */
CREATE TABLE MinUser_Audit (
AuditUser_Id SERIAL,
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL,
ipaddress text NOT NULL,
User_Id int NOT NULL,
User_Name varchar (50) NOT NULL,
User_Password varchar (50) NOT NULL,
User_Email varchar (50),
User_Role varchar (50)
) WITH (OIDS=FALSE);
/* Function that will add a row to the MinUser_audit table when an insertion/deletion/modification is made to the MinUser table */
CREATE OR REPLACE FUNCTION MinUser_audit() RETURNS TRIGGER AS $usr_audit$
BEGIN
--
-- Create a row in MinUser_Audit to reflect the operation performed on MinUser,
-- make use of the special variable TG_OP to work out the operation.
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO MinUser_audit (operation, stamp, userid, ipaddress, User_ID, User_Name, User_Password, User_Email, User_Role) VALUES ('D', now(), user, inet_client_addr(), OLD.User_Id,OLD.User_Name, OLD.User_Email, OLD.User_Password, OLD.User_Role);
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO MinUser_audit (operation, stamp, userid, ipaddress, User_ID, User_Name, User_Password, User_Email, User_Role) VALUES ('U', now(), user, inet_client_addr(), NEW.User_Id,NEW.User_Name, NEW.User_Email, NEW.User_Password, NEW.User_Role);
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO MinUser_audit (operation, stamp, userid, ipaddress, User_ID, User_Name, User_Password, User_Email, User_Role) VALUES ('I', now(), user, inet_client_addr(), NEW.User_Id,NEW.User_Name, NEW.User_Email, NEW.User_Password, NEW.User_Role);
RETURN NEW;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$usr_audit$ LANGUAGE plpgsql;
/* Create the trigger that will use the MinUser_audit() function */
CREATE TRIGGER MinUser_auditt AFTER INSERT OR UPDATE OR DELETE ON MinUser
FOR EACH ROW EXECUTE PROCEDURE MinUser_audit();
I understand how to get the user name of the person who inserted/modified/deleted the record using user. Is there a function I can use to get the ip address as well? Also, is there a reference on PostgreSQL's website that shows all the different server variables that you can use in a query?
