Re: One SQL statement to update multiple tables?

In article <35b4ade6.113448099_at_news>,
  sbffn_at_vgf.vg (Mauro) wrote:
> On Tue, 21 Jul 1998 00:13:09 -0400, "Chris Garland"
> <cgarland_at_nr.infi.net> wrote:
>
> >Can anyone think of a solution (i.e., strange and convoluted SQL
> >statement, some funky Oracle trick,etc.) that will allow us to execute
> >only one ODBC call to update multiple tables?
>
> Sure.
>
> Create a KEYTABLE table with 2 columns:
> - Key (NUMBER) and Value (NUMBER)
>
> and fill it with some values, namely:
> - Insert ... 1,0
> - Insert ... 2,0
> - Insert ... 3,0
>
> then create a trigger on it:
>
> Create trigger ... on update
> referencing old as old new as new ...
> begin
> if :old.key = 1 then
> update ...
> update ...
> end if;
> if :old.key = 2 then
> update ...
> update ...
> end if;
> ....
> end;
>
> then simply do (via ODBC)
>
> UPDATE KEYTABLE SET value=1-value where key=1;
>
> and you're done.
>
> >
> >Thanks,
> >Chris Garland
> >garlandc_at_volvo.com
>
> --
> Mauro.
>
> (My return address is intentionally invalid; ROT13 is required.
> As someone already told this audience, my responses are not to be
> considered official technical support or advice.)
>

You might want to investigate Oracle8 and "Instead of" triggers. Basically you can create a trigger on a view (that view being a join of multiple tables) that updates all the neccessary tables and columns...

-Frank

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Tue Jul 21 1998 - 14:01:23 CDT