[kukuk]

Hi,

Where can I learn about UPSERT command ?
Tx.

[billm]

Hi kukuk,

In the old (!) days you might write

begin
update <table> set <column> = <value> where <primarykey> = <key>;
if sql%rowcount = 0 then
insert into <table> (<column>,<column>) values (<key>,<value>);
end if;
end;

Ie, update an existing row, but if it doesn't exist, insert one.

Some db's have now introduced various syntaxes for doing it directly, ie upsert, merge etc. I believe 9i has such a beast, and would suggest the 9i docs, try otn.oracle.com

Hth
Bill

[kukuk]

Thank you, billm.

I want to know what happens with triggers when I run this <upsert>.
Let's say, my <upsert> updates 3 rows and inserts another 2.
Let's say, I have different triggers after insert and after update.
Will both of them fire ?

[billm]

Hi Kukuk,

To be honest I don't know for certain.

I would imagine that with FOR EACH ROW triggers that the correct trigger would fire, otherwise the trigger code would be illegal. Ie, in an after insert trigger, there are no :OLD values, and referencing them would cause an error. So certainly for these types of triggers I would imagine they have to fire correctly.

For triggers fired after the whole operation, I'm not sure but I would think it isn't quite as important as with "for each row" triggers, as in a traditional bulk insert/update trigger you don't know which specific rows were touched anyway?

The only thing I can suggest is to create a couple and find out? This begs the question is there a new trigger firing type called "after upsert on..." :-)

Hth
Bill

[kukuk]

Thanks, Bill

[clio_usa]

Here is an example:

MERGE INTO sales s
USING new_sales n
ON (s.sales_transaction_id = n.sales_transaction_id)
WHEN MATCHED THEN
UPDATE s_quantity = s_quantity + n_quantity, s_dollar = s_dollar + n_dollar
WHEN NOT MATCHED THEN
INSERT (sales_quantity_sold, sales_dollar_amount)
VALUES (n.sales_quantity_sold, n.sales_dollar_amount);


For more information follow this link Implementing an Efficient MERGE Operation


Hope that helps,

clio_usa
OCP - DBA
Visit our Web Site