Newbe Triger "How To" question

We are in the proces of converting a MS Sql 7.0 database over to Oracle 10g
RAC. The "Migration Workbench" utility was used to convert the database
over, but most of the sprocs and triggers did not convert.

I am currently in the process off working on the triggers and I'm running into problems with the re-write. I'm sure most of these problems are a result of my "lack of Orackle knowledge".

Anyway, When we insert/update a record in one of our tables that same record must be updated with values from alternate tables. Becuase of the way our insert/update process works, we have now way to get this info before hand and include it in the insert/update query.

Currently in our MS Sql 7.0 database we fire a trigger along the logic below:
CREATE TRIGGER tr_TableAInsertUpdate ON dbo.TableA FOR INSERT, UPDATE
AS

   UPDATE A

     SET Field1 = B.Field1,
         Field2 = B.Field2,
         Field3 = C.Field1,
         Field4 = C.Field2 + ' ' + C.Field3
    FROM TableA A INNER JOIN Inserted I ON A.RowId = I.RowId
       LEFT OUTER JOIN TableB B ON B.RowBId = I.RowBId
          LEFT OUTER JOIN Table C on C.RowCId = I.RowCId

In the trigger above, a record (or records) is entered into TableA, the trigger then updates values in TableA with values in "Joined" tables (TableB and TableC).

In Oracle I am having problems implementing similar logic in the trigger. I believe to use some similar logic I would need to do this via a "Statement" level trigger. In a "Row" level trigger I believe this would cause "Mutation" errors.

What would I need to do to implement similar functionality in a "Row" level trigger.

My thought is that I should create a "Before" trigger that gathers all of this additional data into the "NEW" values. If I do this I believe I need to query each of my alternative tables individually and store the results into a Cursor. I could then use the cursor values to populate the "NEW" values and as a result they would get updated in to the table.

Am I on track with my thoughts or am I really off base?

Any additional info on how to solve this problem would be greatly appreciated.

Thanks
Lee Duhl
Realcomp II Ltd Received on Thu Jul 15 2004 - 12:14:28 CDT