Home » SQL & PL/SQL » SQL & PL/SQL » everytime an update is done.. how can i insert?
everytime an update is done.. how can i insert? [message #439835] Tue, 19 January 2010 11:00 Go to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
        UPDATE INTEGRATION.INT_BLAH_TEST A
                    SET AMT = (SELECT A.AMT + SUM(AMT) 
                               FROM INTEGRATION.CASH_TRANS 
                               WHERE A.CASH_BAL_ID = ACCOUNT_ID || '~' || TXN_CCY)
                    WHERE EXISTS (SELECT NULL
                                  FROM INTEGRATION.CASH_TRANS
                                  WHERE a.CASH_BAL_ID = ACCOUNT_ID || '~' || TXN_CCY);


The above code groups a bunch of transactions from INTEGRATION.CASH_TRANS and updates records in the INTEGRATION.INT_BLAH_TEST table.

When a record in the INTEGRATION.INT_BLAH_TEST is updated, how can i make it do an insert into another table at the same time without using a database trigger?
Re: everytime an update is done.. how can i insert? [message #439836 is a reply to message #439835] Tue, 19 January 2010 11:07 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Follow the update statement with an insert/select that uses the same basic where clause as the update.
Re: everytime an update is done.. how can i insert? [message #439837 is a reply to message #439835] Tue, 19 January 2010 11:08 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>how can i make it do an insert into another table at the same time without using a database trigger?
by issuing INSERT statement, perhaps
Re: everytime an update is done.. how can i insert? [message #439840 is a reply to message #439836] Tue, 19 January 2010 11:17 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
cookiemonster wrote on Tue, 19 January 2010 11:07
Follow the update statement with an insert/select that uses the same basic where clause as the update.


so i'd have to repeat code possibly? is there absolutely no other way?

[Updated on: Tue, 19 January 2010 11:18]

Report message to a moderator

Re: everytime an update is done.. how can i insert? [message #439842 is a reply to message #439835] Tue, 19 January 2010 11:34 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well you can do something like this:
FOR rec IN (<select rows to update>) LOOP

  SELECT val_to_update_to
  FROM table2
  WHERE ...

  UPDATE table1
  SET col = val
  WHERE col1 = rec.col1
  
  INSERT INTO table3 VALUES ....

END LOOP;


Doesn't really save you any code though and it'll be less efficient than single update/insert.

While avoiding code re-use is always laudable I think you my be focusing on it a little bit too much.

The other question to ask is why are you duplicating data into another table?
Could be you don't it or the insert.

Re: everytime an update is done.. how can i insert? [message #439923 is a reply to message #439835] Wed, 20 January 2010 03:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
GO on - give us a laugh and tell us why you can't use a trigger - after all, it is the single best solution to the problem you have.

There is another way of doing it, but it is very questionable, and I personaly wouldn't touch it on a bet.

Create a function that takes a number and a rowid as parameters (in your case).
This function will do two things:
1) It will return the number that was passed in.
2) In an autonomous transaction, it will do an insert into the other table that you want to insert into.

If you use functions like this during SELECTS, you can get a wide variety of odd results depending on how the CBO handles the query, but you should be safe using it in the top level of an UPDATE

Here's an example:
create table test_137 (col_1 number, col_2 number);
create table test_138 (col_1 number, col_2 number);

create or replace function do_log(p_num in  number, p_rowid  in  rowid) return number as
  pragma autonomous_transaction;
begin
  insert into test_138 (col_1,col_2)
  select col_1,col_2
  from   test_137
  where  rowid = p_rowid;
  
  commit;
  return p_num;
end do_log;
/

insert into test_137 values (1,1);
insert into test_137 values (1,2);
insert into test_137 values (1,3);
insert into test_137 values (1,4);
insert into test_137 values (2,1);
insert into test_137 values (2,2);

update test_137 t set t.col_2 = do_log(3,t.rowid) where t.col_1 = 1;
Re: everytime an update is done.. how can i insert? [message #439926 is a reply to message #439835] Wed, 20 January 2010 04:10 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
What happens if the user does an update and then a rollback?
You'll get phantom records in the other table.
Plus it's only workable if you want to record the old values, I believe the OP wants the new ones.
Re: everytime an update is done.. how can i insert? [message #439932 is a reply to message #439926] Wed, 20 January 2010 04:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I did say I wouldn't touch it on a bet!!

In the example thatthe user has posted, they could get the new value in the record by passing the new value into the function, and inserting the new value rather than the existing value into table.

You are quite right though - there are many drawbacks to this as a solution, but as the OP's decided to avoid using the simple, reliable solution to the problem, this is the best alternative I can think of.
Re: everytime an update is done.. how can i insert? [message #440029 is a reply to message #439835] Wed, 20 January 2010 12:57 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
Quote:
after all, it is the single best solution to the problem you have


I agree that a trigger solution is "best" for this problem. But we should keep in mind that "best" is a relative term subject to the "eye of the beholder". Triggers both table triggers and instead-of triggers are an advanced feature of oracle and like any advanced feature, they have limitations that must be accepted and managed. For example:

triggers disable parallel dml. Thus any table with a trigger on it cannot participate in a parallel dml operation.

triggers are disabled by direct load operations (at least that is how I remember it) so sqlloader in direct load will not cause triggers to fire.

Knowing this, are triggers still the "best" solution? I agree with JRowbottom that they are but as I said, it all depends upon what you want most.

I mention this because the fact that the OP asked for a solution that does not require triggers means they want something that is important to them above and beyond just any solution to the problem (although they failed to tell us what that something is (too bad, we might could have helped them with that)). Thus, depending upon what this unstated desire is, the trigger solution may not be the best solution for them.

So OP, as was asked before, would you care to elaborate on why you want a solution that does not involve triggers? We are always interested in the reasoning behind why people desire not to use a database feature, especially when that feature seems the obvious choice.

Kevin
Previous Topic: HTML TABLE OUTPUT FORMAT
Next Topic: Return entire records matching aggregated condition?
Goto Forum:
  


Current Time: Wed Sep 28 12:32:38 CDT 2016

Total time taken to generate the page: 0.18781 seconds