Skip site navigation (1) Skip section navigation (2)

Re: Trigger

From: "Raigo Lukk" <l950681(at)ttu(dot)ee>
To: pgsql-general(at)postgreSQL(dot)org
Subject: Re: Trigger
Date: 2000-03-07 18:04:27
Message-ID: 77360BFD1645.AAA3C8F@mail.ttu.ee (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

pgsql-general by date

Next:From: Marten FeldtmannDate: 2000-03-07 18:58:58
Subject: Re: [GENERAL] 50 MB Table
Previous:From: Francis LachapelleDate: 2000-03-07 17:49:51
Subject:

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group