Re: Converting IBM DB2 TRIGGERs to PostgreSQL

Josh... thanks very much for the quick response. PostgreSQL looks to be
very flexible. I will give it a try.

Thanks again.

/Alan

On Wed, 2003-06-11 at 01:25, Josh Berkus wrote:
> 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.