Alan,
> Part of my challenge is to somehow convert the following (sample)
> TRIGGER statements that work just fine under DB2.
Easy. Read the part in the "Procedural Languages" section in the online docs
on "PL/pgSQL Triggers"
To do what you want .....
CREATE FUNCTION tf_alien_log () RETURNS TRIGGER AS '
DECLARE op_var TEXT;
create_date TIMESTAMP;
BEGIN
IF TG_OP = ''UPDATE'' THEN
op_var := ''UPD'';
create_date := NEW.created;
ELSE
op_var := ''INS'';
create_date := CURRENT_TIMESTAMP;
END IF;
INSERT INTO alien.country_log
VALUES (NEW.countryid, NEW.fullname, NEW.code, NEW.undefined,
NEW.markfordelete, NEW.userid,
create_date, CURRENT_TIMESTAMP, op_var);
RETURN NEW;
END; ' LANGUAGE 'plpgsql';
CREATE TRIGGER tg_alien_log AFTER INSERT OR UPDATE ON alien.country
FOR EACH ROW EXECUTE tg_alien_log();
Easy, neh? Plus in the future it will become possible to write triggers in
other langauges, such as TCL and Perl.
BTW, the "BEGIN ATOMIC" is superfluous in PostgreSQL; triggers are atomic by
definition.
--
Josh Berkus
Aglio Database Solutions
San Francisco