Skip site navigation (1) Skip section navigation (2)

Re: Date Of Entry and Date Of Change

From: Robert Wimmer <seppwimmer(at)hotmail(dot)com>
To: Dale Seaburg <kg5lt(at)verizon(dot)net>, <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Date Of Entry and Date Of Change
Date: 2008-08-31 08:23:05
Message-ID: BAY139-W7DBE45CAEA47E98C79445D05D0@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-novice

>>> 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/

In response to

pgsql-novice by date

Next:From: Sean DavisDate: 2008-08-31 09:39:00
Subject: Re: Date Of Entry and Date Of Change
Previous:From: richard terryDate: 2008-08-31 02:43:45
Subject: Re: How to save a image file in a postgres data field.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group