Newbe Oracle Trigger Question

From: Lee <lduhl_at_corp.realcomp.com>
Date: 15 Jul 2004 06:45:32 -0700
Message-ID: <719532c5.0407150545.75beffbc@posting.google.com>

We are in the process of converting a MSSql 7.0 db to Oracle 10g and I am running into problems with some of the trigger conversions.

I have a table that once a record is inserted or updated also needs to have values within that same record populated with data from other tables.

Becuase of the way the insert/update process works we are unable to perform this logic at the time the record is inserted/updated so it must be done via a trigger.

MS SQL Sample:
CREATE TRIGGER tr_TableAInsertUpdate ON dbo.TableA FOR INSERT, UPDATE
AS

UPDATE A

   SET   Field1 = B.MainName,
         Field2 = C.AssociateName,
         Field3 = C.AssociateAddress,
         Field4 = D.OfficeName,
         Field5 = D.OfficeAddress

FROM TableA INNER JOIN Inserted I ON A.TableAId = I.TableAId LEFT JOIN TableB B ON I.TableBId = B.TableBId

      LEFT JOIN TableC C ON I.TableCId = C.TableCId
        LEFT JOIN TableC D ON I.TableDId = D.TableDId

Can this same type of logic be used in an Oracle 10g trigger? If not, how would I go about writing it for Oracle?

Also, our inserts/updates are generally single record so I was expecting to use a "Row" based trigger instead of a "Statement" based trigger. Is this the way to go? Any recomendations on when a "Row" verse "Statement" trigger should be used. What about "Before" or "After" what logic should be used to determine this?

Thanks for any help you can supply.

Thanks
Lee Duhl
Realcomp II Ltd. Received on Thu Jul 15 2004 - 08:45:32 CDT

Newbe Oracle Trigger Question - How to