Thread: Transactional behaviour with trigger
Hi, I create two records in a transaction in two different tables: customer and contactaddress. customer has a foreign key to contactaddress. I also have third table, customeraddress, which is a materialized view of the join of customer and contactaddress For the table customer I have a trigger(function) that inserts a record in this third table (which works OK as the record is added): .... INSERT INTO customeraddress(customer, customernumber, lastname, zipcode, city,housenumber, address) VALUES(NEW.objectid,NEW.customernumber,NEW.lastname,NULL,NULL,NULL,NULL); For the contactaddress I also have a trigger(function) that should update the record in the third table with the additional info of the contactaddress: .... SELECT INTO customer_record objectid FROM prototype.customers WHERE contactaddress = NEW.objectid; IF customer_record.objectid IS NOT NULL THEN UPDATE customeraddress set zipcode = NEW.zipcode, city = NEW.city, housenumber = NEW.housenumber , address = NEW.objectid WHERE customer = customer_record.objectid; This last trigger is executed but customer_record.objectid seems to be always NULL as if the the customer record is not visible. Is that correct or is there something wrong with the SQl above? In other words: 1. start transaction 2. add customer record 3. fire customer insert trigger -> copy record to materialized view table 4. add contactaddress record 5. fire contactaddress insert trigger -> update record in materialized view table -> no customer record??? 6. end transction TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl
On Sun, Sep 17, 2006 at 02:32:10PM +0200, Joost Kraaijeveld wrote: > For the contactaddress I also have a trigger(function) that should > update the record in the third table with the additional info of the > contactaddress: > > .... > SELECT INTO customer_record objectid FROM prototype.customers WHERE contactaddress = NEW.objectid; > IF customer_record.objectid IS NOT NULL THEN > UPDATE customeraddress set zipcode = NEW.zipcode, city = NEW.city, housenumber = NEW.housenumber , address = NEW.objectid > WHERE customer = customer_record.objectid; > > This last trigger is executed but customer_record.objectid seems to be > always NULL as if the the customer record is not visible. Is that > correct or is there something wrong with the SQl above? Where does prototype.customers fit in? Are you sure it has a row that matches the WHERE clause? Where would that row have come from? Is there a reason you're maintaining customeraddress as a materialized view rather than as a "real" view? -- Michael Fuhr
Hi Michael, Thanks for answering. On Sun, 2006-09-17 at 08:36 -0600, Michael Fuhr wrote: > On Sun, Sep 17, 2006 at 02:32:10PM +0200, Joost Kraaijeveld wrote: > Where does prototype.customers fit in? Are you sure it has a row > that matches the WHERE clause? Where would that row have come from? I have solved it by rethinking what was actually happening and boosting the traces of JBoss to see what it actually did. It actually did this: 1. start transaction 2. add customer record 3. add contactaddress record 4 update the customer record <--- !!!! 5. end transction So I moved the update of the materialized view to an update trigger of the customer table and now it works. > Is there a reason you're maintaining customeraddress as a materialized > view rather than as a "real" view? Pure performance reasons. -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl