Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

For the IP address you can use the function inet_client_addr()

All the system information functions are documented in the manual in the chapter "System Information Functions"

http://www.postgresql.org/docs/current/static/functions-info.html

share|improve this answer
    
Thank you that did the trick. –  Craig Efrein Dec 16 '11 at 14:48
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.