Обсуждение: 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

* Raigo Lukk <l950681@ttu.ee> [000307 05:36] wrote:
> 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.

I'm pretty new to postgresql as well, but i think what you want to
look at is the RULES section of the documentation, it seems that
would be what you're looking for.

I haven't done it myself, but there's some decent examples there.

--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]

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