PHPBuilder - Postgresql trigger: last modified time



RSS Twitter
Snippets Databases

Postgresql trigger: last modified time

by: Scott Marlowe
|
April 2, 2003

Version: 1.0

Type: Sample Code (HOWTO)

Category: Databases

License: BSD License

Description: This is an unltra simple trigger written in plpgsql, the standard procedural language of postgresql. It updates the lm (last modified field) of a table every time a record is inserted or updated. Requires plpgsql.



-- FUNCTION --

CREATE FUNCTION modtime () RETURNS opaque AS '
    BEGIN
        new.lm :=''now'';
        RETURN new;
    END;
' LANGUAGE 'plpgsql';

-- TABLE --

CREATE TABLE dtest (
    id int primary key,
    fluff text,
    lm timestamp without time zone
);


--TRIGGER --

CREATE TRIGGER dtest 
  BEFORE UPDATE or INSERT ON dtest FOR EACH ROW EXECUTE PROCEDURE
    modtime(lm);

-- SQL TESTS --

INSERT INTO dtest (id, fluff) VALUES (1,'this is a test');
INSERT INTO dtest (id, fluff) VALUES (2,'this is another test');
SELECT * FROM dtest;
  1 | this is a test       | 2003-04-02 10:33:12.577089
  2 | this is another test | 2003-04-02 10:33:18.591148
UPDATE dtest SET id=3 WHERE id=1;
  3 | this is a test | 2003-04-02 10:34:52.219963  [1]
UPDATE dtest SET fluff='now is the time' WHERE id=2;
SELECT * FROM dtest WHERE id=2;
  2 | now is the time | 2003-04-02 10:38:06.259443 [2]
UPDATE dtest SET lm='2003-04-02 08:30:00' WHERE id=3;
SELECT * FROM dtest WHERE id=3;
  3 | this is a test | 2003-04-02 10:36:15.45687 [3]

[1] The timestamp has changed for this record when we changed the id field.
[2] The timestamp also changes for the fluff field.
[3] We tried to set lm, but the trigger on that field in dtest intercepted the change and forced it 

Comment and Contribute

Your comment has been submitted and is pending approval.

Author:
Scott Marlowe

Comment:



Comment:

(Maximum characters: 1200). You have characters left.