On a related note

For users of Postgres 7.2 (and probably up to 7.3, but it might be compatible with all higher versions) who do not have a clue how to get those triggers going because it’s all undocumented, here goes.

Two things you need to know first:

1. Triggers rely on functions created by users.
2. Functions (in 7.2) for triggers MUST return a result of OPAQUE.
3. Triggered functions can not have parameters!

Right, tell me you didn’t know that and go back to start.

Take the following simple (and not real-life) scenario:

We have a table ‘currency’ that needs to have an exact copy, ‘back_currency’. Everytime something is added to currency, it should be copied to the backup table.

First we create a function called ‘currencyupdate’:

CREATE FUNCTION currencyupd() RETURNS opaque AS ‘

BEGIN

   insert into back_currency (

    currency_code,

    currency_description,

    user_lada) VALUES

    (

     NEW.currency_code,

     NEW.currency_description,

     current_timestamp

    );

   RETURN NEW;

END;

‘  LANGUAGE ‘plpgsql’ WITH (ISSTRICT);

(note: if we were using a DELETE/UPDATE trigger, older recordsets are stored in the OLD object).

Now we can activate the trigger by sending the following query to the Postgres server:

CREATE TRIGGER OnCurrencyUpdate BEFORE INSERT ON currency

	FOR EACH ROW EXECUTE PROCEDURE currencyupd();

Just in case you needed to know.