Thread: RE: [GENERAL] Trigger

Switch to v7.0 beta1 and use FOREIGN KEY. You will save a lot of time.
Before you really finish your development there will be 7.0 production (this
is my private estimation).
Andrzej Mazurkiewicz

> -----Original Message-----
> From:    Raigo Lukk [SMTP:l950681@ttu.ee]
> Sent:    7 marca 2000 13:55
> To:    pgsql-general@postgreSQL.org
> Subject:    [GENERAL] Trigger
>
> Hi
>
> I am trying to create a trigger for a detail table to check that value
> what is entered exists in master table.
> For example into CustomerOrder table could not be enterd order for
> Customer what does not exists.
>
> How is it best done in PostgreSQL?
>
> I believe a trigger is what I must do.
> I looked the documentation in the part of triggers and there was an
> trigger example what had a part:
> execute procedure procedurename
> Does this mean that I should create a procedure for each trigger?
> Going further I found no Create Procedure in the documentation ... so
>  how to create procedures in PostgreSQL?
>
>
> Maybe, somebody could send me some example trigger for checking detail
> record value's existance in master table.
>
> Sorry, for simple question, but I just started using PostgreSQL.
>
>
> Thanks
> Raigo
> raigo.lukk@ttu.ee
>
>
> ************

Hi

On Tue, 7 Mar 2000, Andrzej Mazurkiewicz wrote:

> Switch to v7.0 beta1 and use FOREIGN KEY. You will save a lot of time.
> Before you really finish your development there will be 7.0 production (this
> is my private estimation).
> Andrzej Mazurkiewicz

I am now using PostgreSQL 6.5.3 in RedHat 5.2
In my case the upgrade to v.7 is not possible because I am doing it for
university project and in university computers I have no rights to
install stuff :-(

Still, I would appreciate some examples on triggers and procedures as
I will need to write some procedures during this project.


Raigo

> Switch to v7.0 beta1 and use FOREIGN KEY. You will save a lot of time.
> Before you really finish your development there will be 7.0 production (this
> is my private estimation).
> Andrzej Mazurkiewicz
>
> > I am trying to create a trigger for a detail table to check that value
> > what is entered exists in master table.
> > For example into CustomerOrder table could not be enterd order for
> > Customer what does not exists.


    Not only that he'll save some time. Without the deferred trigger
    queue manager, watching that one and the same row cannot be modified
    twice inside of one transaction (WRT key changes), it's possible to
    fool the trigger and end up with inconsistent data in a multiuser
    environment.

    And don't forget that the INSERT of the FK and the DELETE of the PK
    can happen simultaneously. Due to MVCC, both transactions don't
    know about each other (thanks to Vadim).


Jan from Philly

--
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Hi

>     twice inside of one transaction (WRT key changes), it's possible to
>     fool the trigger and end up with inconsistent data in a multiuser
>     environment.

OK let's assume that customer is not deleted while order is
enetered. At my work I have used InterBase quite a lot. Up to IB 5
there was also no foreign keys, so it had to be done with triggers
just like in PostgreSQL.  So when using IB 4 I usually did
something like this:

CREATE EXCEPTION CHILD_INSERT_RESTRICT "Cannot
INSERT ORDER because CUSTOMER does not exist in
CUSTOMER table";


CREATE TRIGGER BI_ORDER FOR ORDER BEFORE INSERT
AS

DECLARE VARIABLE numrows INTEGER;
BEGIN
    select count(distinct CustomerID)
      from customer
      where CustomerID=NEW.CustomerID
      into numrows;
    IF (numrows = 0 ) THEN
    BEGIN
      EXCEPTION CHILD_INSERT_RESTRICT;
    END

END

I think there is some small syntax differences in PostgreSQL.
So, can somebody please put this into PostgreSQL syntax for me?
I searched the manuals but found no Exeption ... is it possible to
use Exceptions in PostgreSQL?
Also, does there excist special aliases like NEW and OLD in
PostgreSQL?


Thanks
Raigo