Обсуждение: Converting IBM DB2 TRIGGERs to PostgreSQL

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

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

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

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.

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

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