Re: Trigger - Mailing list pgsql-general

From Raigo Lukk
Subject Re: Trigger
Date
Msg-id 77360C5D27.AAA1517@mail.ttu.ee
Whole thread Raw
In response to Trigger  (Raigo Lukk <l950681@ttu.ee>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Gabriel Fernandez
Date:
Subject: psql frontend: up arrow key and history utility
Next
From: "Steve Wolfe"
Date:
Subject: Postmaster startup options (was: 50 MB Table)