我已经设置了一个脚本,该脚本是从网络上的各种链接创建的,以使用触发器跟踪表中的活动。
/* 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();
我了解如何获取使用用户插入/修改/删除记录的人的用户名。有没有我可以用来获取IP地址的功能?此外,PostgreSQL 的网站上是否有显示您可以在查询中使用的所有不同服务器变量的参考?
对于IP地址,您可以使用该功能
inet_client_addr()
所有系统信息功能都记录在手册的“系统信息功能”一章中
http://www.postgresql.org/docs/current/static/functions-info.html