From: Bob K. <bo...@ip...> - 2002-11-12 05:21:30
|
On Monday 11 November 2002 08:04 pm, py...@ru... wrote: <snip> > Can someone point me in the right direction here? I basically want to > do: > > open a new transaction > > insert row into table 1 > > insert multiple rows into table 2 > these inserts may be duplicates > > insert multiple rows into table 3, each of which is tied > back to table 1 by an ID. > > commit the transaction > > For all statements except the inserts into table 2, if I get an error > I would like to rollback the transaction. <snip> Well, I think that PostgreSQL is behaving exactly as expected by an RDBMS= =20 performing a transaction. I use a similar technique in a Java app, but on= ly=20 for the part involving the inserts into row 2. However, the key differenc= e is=20 that I'm able to make each insert a separate transaction. Perhaps you could use a rule to achieve what you want. You could create a= =20 clone of table 2 augmented with a rule that checks to see if the row you = just=20 inserted into the clone exists already in table 2. If so, it just drops t= he=20 new row. If not, it inserts the row into table 2. Basically, the clone ta= ble=20 just consumes each row inserted into it. You replace the "insert multiple= =20 rows into table 2" part with "insert multiple rows into table 2's clone."= I'm=20 not sure this approach will work, but it seems to be worth a try. Regards, =2E... Bob |