Re: Newbe Triger "How To" question

Lee wrote:

> 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

I was pretty comfortable with what you wrote right up until you said store the results in a cursor: Why?

But in general I think you are approaching it correctly. For examples go to http://www.psoug.com.
Click on Morgan's Library
Search for Table Triggers for demos

Daniel Morgan Received on Thu Jul 15 2004 - 21:02:50 CDT