Thread: Converting IBM DB2 TRIGGERs to PostgreSQL

Converting IBM DB2 TRIGGERs to PostgreSQL

From
Alan Searles
Date:
I am relatively new to PostgreSQL, and I am currently trying to convert
a JAVA application that currently runs under IBM's DB2.

Part of my challenge is to somehow convert the following (sample)
TRIGGER statements that work just fine under DB2.

------------------------------------------------------------------------------------------------
CREATE TRIGGER ALIEN.COUNTRY_INS
         AFTER INSERT ON ALIEN.COUNTRY
         REFERENCING NEW AS X
         FOR EACH ROW MODE DB2SQL
         BEGIN ATOMIC
           INSERT INTO ALIEN.COUNTRY_LOG
                  VALUES (X.COUNTRYID, X.FULLNAME, X.CODE,
                          X.UNDEFINED, X.MARKFORDELETE, X.USERID,
                          CURRENT TIMESTAMP, CURRENT TIMESTAMP, 'INS');
         END

CREATE TRIGGER ALIEN.COUNTRY_UPD
         AFTER UPDATE ON ALIEN.COUNTRY
         REFERENCING NEW AS X
         FOR EACH ROW MODE DB2SQL
         BEGIN ATOMIC
           INSERT INTO ALIEN.COUNTRY_LOG
                  VALUES (X.COUNTRYID, X.FULLNAME, X.CODE,
                          X.UNDEFINED, X.MARKFORDELETE, X.USERID,
                          CREATED, CURRENT TIMESTAMP, 'UPD');
         END

------------------------------------------------------------------------------------------------
Can this be done with PostgreSQL ?  I am concerned more with the "BEGIN
ATOMIC" sections.

Any help would be appreciated. Thanks.

/Alan


Re: Converting IBM DB2 TRIGGERs to PostgreSQL

From
Josh Berkus
Date:
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

Inheritance question

From
Joanne Formoso
Date:
I'm completely novice when it comes to databases so
please bear with me.

The database application I'm doing has something to do
with putting personal records in the database.  I
separated the personal information and the
professional information by creating two different
tables in the database.  The two tables are linked
through an P_ID foreign key.

My problem is I've added two constraints: namely

ALTER TABLE professional_data add constraint (test1)
foreign key (prof_id) references personal_data (p_id)
on update cascade;

and

ALTER TABLE professional_data add constraint (test2)
foreign key (prof_id) references personal_data (p_id)
on delete cascade;

When I change the p_id in the personal_data table the
prof_id is also changed in the professional_data
table.  But when I try to delete a record that in the
personal_data table the record can't be deleted.

Postgresql gives this errors:

test1 referential integrity violation - key in
personal_data still referenced from professional_data

Is there anyway for me to combine both the update
cascade and delete cascade in one foreign key
constraint?

Thanks,
Joanne

__________________________________
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com

Re: Converting IBM DB2 TRIGGERs to PostgreSQL

From
Alan Searles
Date:
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.


Re: Converting IBM DB2 TRIGGERs to PostgreSQL

From
Alan Searles
Date:
Robert...you are probably under the impression that I some sort of
expert in DB2 -- that would be incorrect !! Over the years. I have
scratched my way to gain some understanding of it. That's it.

When it comes to "BEGIN ATOMIC" references within TRIGGER statements...I
have NO idea. Where/Why the reference to "ATOMIC" I can not guess.
Sounds impressive though.

/Alan

On Thu, 2003-06-12 at 11:32, Robert Treat wrote:
> On Wed, 2003-06-11 at 01:25, Josh Berkus wrote:
> >
> > BTW, the "BEGIN ATOMIC" is superfluous in PostgreSQL; triggers are atomic by
> > definition.
> >
>
> Hey Alan, is there any other type of "BEGIN's" that are allowed in DB2
> functions?
>
> Robert Treat


Re: Converting IBM DB2 TRIGGERs to PostgreSQL

From
Robert Treat
Date:
On Wed, 2003-06-11 at 01:25, Josh Berkus wrote:
>
> BTW, the "BEGIN ATOMIC" is superfluous in PostgreSQL; triggers are atomic by
> definition.
>

Hey Alan, is there any other type of "BEGIN's" that are allowed in DB2
functions?

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL