Re: triggers - Mailing list pgsql-novice

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Sure, here's a snippet from something I'm writing. This should be more
than enough to get you off the ground. There are other neat tricks you can
do with 'GET DIAGNOSTICS ... = ROW_COUNT' to verify that stuff actually
happened.

DROP SEQUENCE AuditSeq;
CREATE SEQUENCE AuditSeq;

DROP TABLE Audit;
CREATE TABLE Audit (
    AuditSeq INTEGER NOT NULL,
    UpdatedBy TEXT NOT NULL,
    Created TIMESTAMP NOT NULL,
    Modified TIMESTAMP NOT NULL
);

Other tables then inherit from the Audit table. The idea is to have a
standarized method of following updates across a bunch of tables. It also
allows direct querying on the audit table to see who is doing what updates
to which tables.

DROP VIEW AuditList;
CREATE VIEW AuditList AS
    SELECT pg_class.relname, Audit.UpdatedBy, Audit.Created,
        CASE    WHEN Audit.Created = Created.Modified THEN NULL
            ELSE Audit.Modified
        END AS Modified
    FROM pg_class, Audit
    WHERE Audit.tableoid = pg_class.oid
    ORDER BY AuditSeq;

And now the triggers to make it sane. The table OrgPeople inherits from
Audit. The triggers are generic enough to work with any and all child
tables of Audit.

- -- DROP FUNCTION AuditIns();
CREATE FUNCTION AuditIns() RETURNS OPAQUE AS '
BEGIN
    NEW.Created = current_timestamp;
    NEW.Modified = NEW.Created;
    NEW.AuditSeq = nextval(''AuditSeq'');
END;
' LANGUAGE 'plpgsql' WITH (isstrict);

CREATE TRIGGER OrgPeopleIns BEFORE INSERT ON OrgPeople
    FOR EACH ROW EXECUTE PROCEDURE AuditIns();



- -- DROP FUNCTION AuditUpd();
CREATE FUNCTION AuditUpd() RETURNS OPAQUE AS '
BEGIN
    NEW.Created = OLD.Created;
    NEW.Modified = current_timestamp;
    NEW.AuditSeq = nextval(''AuditSeq'');
END;
' LANGUAGE 'plpgsql' WITH (isstrict);

CREATE TRIGGER OrgPeopleUpd BEFORE UPDATE ON OrgPeople
    FOR EACH ROW EXECUTE PROCEDURE AuditUpd();


Joshua b. Jore
http://www.greentechnologist.org

On Mon, 22 Apr 2002, paul butler wrote:

> Dear list,
> Just starting out with postgresql, I've got a 18 table db listing info
> on organisations and on the main organisation table I've got an
> update column (timestamp), to keep a record of the last time
> information was updated for each organisation.
> Obviously I can do it on the client app, but I think table /row
> triggers might be a better solution
> is there a straight forward way of, on updating any of the
> organisation tables I can update the update column for that
> organisation?
>
> eg On update orgsubtable wher orgId = X
>     trigger update orgMainTable.timestamp Values(now()) where
> orgId = X
>
> TIA
>
> Paul Butler
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (OpenBSD)
Comment: For info see http://www.gnupg.org

iD8DBQE8xAx4fexLsowstzcRAvajAKCOE6EwDmY2mmlJGlfhNX+cOjv72wCcCZRZ
ull8arKcuJqQpIWiBMnInlg=
=Bnex
-----END PGP SIGNATURE-----