Thread: 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