4)If you ROLLBACK the transaction before committing to the database, the data inserted to the table is also rolled back.There are two types of triggers based on the which level it is triggered.We can create a trigger to update the 'product_price_history' table when the price of the product is updated in the 'product' table.

pl sql inserting updating-14

SQL select * from Employee 2 / ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION ---- -------------------- -------------------- --------- --------- ---------- ---------- --------------- 01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected.

This statement can be used with any interactive tool, such as SQL*Plus or Enterprise Manager.

So if the same date exists in TABLEB, i'd like for it to take teh current value in AMOUNT and add it to the existing value. Thanks for any guidance and if there's a quicker method to do this besides using a PL/SQL, that would be great too. how can I insert into a table with multiple primary keys... insert into table B select trunc(mydate), myname, sum(amount) from table A where trunc(mydate) not in (select trunc(mydate) from table B ) group by trunc(mydate), myname; commit; In the " where trunc(mydate) not in (select....)" I want to add another field... "where trunc(mydate), myname not in (select..." Thanks!

JEIf you have a unigue constraint on the table you can also use an exception.

This would reduce the number of executions needed by the If statement.

Begin Insert Into table Values(); Exception When Dup_Value_On_Index then Update Table Set = Where; End; HIHCOULD YOU SHOW ME A REAL EXAMPLE? Begin Insert Into table2 Values(select mydate, amount from my table1); Exception When Dup_Value_On_Index then Update table2 Set amount = table1.amount amount Where mydate = table1.mydate ; End; Begin Insert Into table2 Select date, amount From table1 Where date = something; Commit; Exception -- Insert failed, so update record When Dup_Value_On_Index Then Begin Update table2 Set amount = amount -- add amount from sub query (Select t.amount From table1 t Where = something) Where date = something; Commit; End; -- end exception End; -- end programupdate table B set (amount) = (select amount from table A where table A.mydate = table B.mydate) where mydate in (select mydate from table A); This still would not take my current value in TABLEB and increment it.. TABLEA mydate amount -------- --------- 3/10 200 TABLEB mydate amount -------- --------- 3/10 1000 after my update.. TABLEB mydate amount -------- -------- 3/10 1200 My primary key being "mydate" Thanks!

1) Row level trigger - An event is triggered for each row upated, inserted or deleted.

2) Statement level trigger - An event is triggered for each sql statement executed.

Maybe there will be no clear winner, but there often is.