Re: Trigger

Hi

I sent this message already, but did not get it back from list. So I
aplogise if somebody gets it two times.

>     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