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.