Re: How to add joins in triggers and cascading triggers

"sb" <sonali.bhavsar_at_gmail.com> wrote in message news:1134382892.800754.270140_at_g49g2000cwa.googlegroups.com...
> Hi,
>
> I am new to oracle and I am facing a dilemma for now. I have 4
> tableslaid out in a following fashion.
>
> Table 1: Primary Key User_ID maps to table 2 's user_id and profile_id
> Table 2: Has 2 primary keys user_id and profile_id
> Table3: Uses table 2's profile id and has an additional column desc
> Table 4:Is triggered by Table 1 (where the trigger is defined) to
> populate duplicate fields in Table 4. Now in Table 4 I have to retrieve
> Profile_ID and Desc from Table 3 via table 2 which has the user_id
> unique to both table 1and table 2. As the trigger exists in table 1and
> profile_id is not present in Table 1, I need a join to gather this
> info.
>
> My questions are:
> Can I create nested triggersto gather info during a trigger from
> various tables?
> What about cursors?Do I need multiple cursors as well?
>
> Any advise will be greatly appreciated.
>

i think this is what you are descrbing (pk's flagged with '*')

    t1(*user_id)
    t2(*user_id, *profile_id)
    t3(*profile_id, description)
    t4(profile_id, description)

1. a table cannot have 2 primary keys... it can have a composite primary key with 2 (or more columns), which is what i'm assuming you meant. this makes t2 an 'associative' or 'intersection' table mapping a many-to-many relationship with t1 and t3. however, this usually supports >1 entry for each column of the composite PK
2. it sounds like t2 contains derived data... typically this should not exist in a relational database unless it is necessary for performance reasons (and well documented as such) or if there is some time factor stored with it, making it an audit trail or historical snapshot that persists when the source data changes.
3. assuming there is a valid reason to copy the data to t4, this can simply be down with an INSERT INTO ... SELECT FROM statement in a trigger on t1 -- but you've not specified when the t4 row should be created, what should be done i it already exists, and when it should be deleted

make sure there's a valid reason for t4's derived data, and determine it's life cycle (see #3 above), then determine what should happen in t1 insert, update, and possible delete triggers... and don't forget to handle exceptions

++ mcs Received on Mon Dec 12 2005 - 05:50:47 CST