Обсуждение: 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 added to my table two columns, named 'doe' and 'doc' respectively. For sake of discussion, let's call the Table 'instr'. What would be the best method of added the current timestamp (date and time) to each of these two new columns. In my mind, the timestamp would need to be added just before the row was INSERTed or UPDATEd to prevent any "race" condition. 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. Regards, Dale Seaburg
On Aug 30, 2008, at 8:56 PM, Sean Davis wrote: > On Sat, Aug 30, 2008 at 5:49 PM, Dale Seaburg <kg5lt@verizon.net> > wrote: >> 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 added to my table two >> columns, named >> 'doe' and 'doc' respectively. For sake of discussion, let's call >> the Table >> 'instr'. What would be the best method of added the current >> timestamp (date >> and time) to each of these two new columns. In my mind, the >> timestamp would >> need to be added just before the row was INSERTed or UPDATEd to >> prevent any >> "race" condition. >> >> 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. > > You can set the default for those columns to current_timestamp, as a > start. Then, you can use an on update trigger for setting the on > update column. Alternatively, you can just use current_timestamp as > the value for updates to your update column. > > See here: > > http://www.postgresql.org/docs/8.3/static/functions-datetime.html > > And here: > > http://www.postgresql.org/docs/8.3/static/sql-createtable.html > > And, finally, here: > > http://www.postgresql.org/docs/8.3/static/plpgsql-trigger.html > > Hope that helps. > > Sean Yes, the default values should have been obvious, but I was attempting to make it too complicated. The last reference you gave, I found too in the pgAdmin III helps. But, when I attempt to create a Trigger Function in pgAdmin, to implement the UPDATE function, I get a message in the SQL tab of that window saying "-- definition incomplete". When I try to get Help, I am pointed to a "404-like code" in the Help screen. No matter what I do in trying to create a Trigger Function, I get nowhere. My postgresql is 8.2 as reported by pgAdmin. I assume I can create Trigger Functions in pgAdmin III. Perhaps not. Dale Seaburg
>>> 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/
On Sat, Aug 30, 2008 at 10:43 PM, Dale Seaburg <kg5lt@verizon.net> wrote: > > On Aug 30, 2008, at 8:56 PM, Sean Davis wrote: > >> On Sat, Aug 30, 2008 at 5:49 PM, Dale Seaburg <kg5lt@verizon.net> wrote: >>> >>> 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 added to my table two columns, >>> named >>> 'doe' and 'doc' respectively. For sake of discussion, let's call the >>> Table >>> 'instr'. What would be the best method of added the current timestamp >>> (date >>> and time) to each of these two new columns. In my mind, the timestamp >>> would >>> need to be added just before the row was INSERTed or UPDATEd to prevent >>> any >>> "race" condition. >>> >>> 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. >> >> You can set the default for those columns to current_timestamp, as a >> start. Then, you can use an on update trigger for setting the on >> update column. Alternatively, you can just use current_timestamp as >> the value for updates to your update column. >> >> See here: >> >> http://www.postgresql.org/docs/8.3/static/functions-datetime.html >> >> And here: >> >> http://www.postgresql.org/docs/8.3/static/sql-createtable.html >> >> And, finally, here: >> >> http://www.postgresql.org/docs/8.3/static/plpgsql-trigger.html >> >> Hope that helps. >> >> Sean > > Yes, the default values should have been obvious, but I was attempting to > make it too complicated. > > The last reference you gave, I found too in the pgAdmin III helps. But, > when I attempt to create a Trigger Function in pgAdmin, to implement the > UPDATE function, I get a message in the SQL tab of that window saying "-- > definition incomplete". When I try to get Help, I am pointed to a "404-like > code" in the Help screen. No matter what I do in trying to create a Trigger > Function, I get nowhere. My postgresql is 8.2 as reported by pgAdmin. > > I assume I can create Trigger Functions in pgAdmin III. Perhaps not. Hi, Dale. Try pasting this into a pgAdminIII sql window: create table abc ( id serial primary key, doe timestamp default current_timestamp, dou timestamp default current_timestamp, val varchar ); create or replace function abc_trig_fn() returns trigger as $$ BEGIN NEW.dou=current_timestamp; IF (TG_OP='INSERT') THEN NEW.doe=current_timestamp; END IF; RETURN NEW; END $$ language plpgsql; CREATE TRIGGER abc_trig BEFORE INSERT OR UPDATE ON abc FOR EACH ROW EXECUTE PROCEDURE abc_trig_fn(); BEGIN; insert into abc(val) values ('abc'); insert into abc(val) values ('123'); insert into abc(val) values ('xyz'); END; select * from abc; update abc set val='def' where val='123'; select * from abc; Sean