Re: Date Of Entry and Date Of Change

>>> I need to be able to establish the Date of Entry (INSERT) and Date
>>> of Change
>>> (UPDATE) of a row to a table
>>> I have looked at Triggers and Functions in the pgAdmin helps, but
>>> it is
>>> confusing at best, how to arrive at a solution. Any help would be
>>> appreciated. Perhaps, a simple example to get me headed in the right
>>> direction.
>>

this is an example from an productive solution. it also includes a logging solution.

CREATE TABLE entry.log (
entryId BIGINT,
dbuser NAME,
op NAME,
stamp TIMESTAMP NOT NULL DEFAULT LOCALTIMESTAMP
);

CREATE TABLE entry.entry (
id BIGSERIAL PRIMARY KEY,
dbtable NAME NOT NULL,
creator NAME NOT NULL,
modifier NAME NOT NULL,
created TIMESTAMP NOT NULL DEFAULT LOCALTIMESTAMP,
modified TIMESTAMP NOT NULL DEFAULT LOCALTIMESTAMP,
... your data
);

CREATE FUNCTION entry.entry_before_trigger() RETURNS TRIGGER AS $$
BEGIN

IF TG_OP = 'INSERT' THEN
NEW.creator := CURRENT_USER;
NEW.modifier := CURRENT_USER;
END IF;
IF TG_OP = 'UPDATE' THEN
NEW.modifier := CURRENT_USER;
NEW.modified := LOCALTIMESTAMP;
END IF;

RETURN NEW;

END; $$
LANGUAGE plpgsql;

CREATE FUNCTION entry.entry_after_trigger() RETURNS TRIGGER AS $$
DECLARE pId BIGINT;
BEGIN

IF tg_op = 'DELETE' THEN pId := OLD.id;
ELSE pId := NEW.id; END IF;

INSERT INTO entry.log(entryid,dbuser,dbtable,op) VALUES(pId,CURRENT_USER,TG_RELNAME,TG_OP);

NOTIFY entry_changed;

RETURN NULL;

END; $$
LANGUAGE plpgsql;

CREATE TRIGGER entry_before_trigger BEFORE insert OR UPDATE ON entry.entry
FOR EACH ROW EXECUTE PROCUDURE entry.entry_before_trigger;

CREATE TRIGGER entry_after_trigger AFTER INSERT OR UPDATE OR DELETE ON entry.entry
FOR EACH ROW EXECUTE PROCUDURE entry.entry_before_trigger;

i hope thet helps

regards sepp

_________________________________________________________________
Es ist höchste Zeit dabei zu sein - Holen Sie sich jetzt die neue Generation der Windows Live Services!
http://get.live.com/