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/