Trigger with embedded select that returns multiple rows

[yodaa]

Hi,

I have a problem with a trigger. Three tables are invovled in the process.

Table a
pID, aID

Table b
pID, bID, bNum, bType

The tables above are the source tables and shall be used to populate Table c. Table c should contain values from the tables above as seen below.

Table c
pID, aID, bID, bNum

I want to make a select statement in the trigger:

select a.pID, a.aID, b.bID, b.bNum from a, b where b.Type == 'Type A' and a.aID not in (select aID from c)

and populate table c with the rows from the select statement.

Is this possible? I can't figure out how to deal with the fact that the select statement will return more than one row.

Any one out there that could give some tips on this?

Thanks a million,
yodaa

 

[SantaMufasa]

Yodaa,

First, in all my (27) years using Oracle, I've never seen the "==" syntax. What do you expect that to be doing?

Also, Unless you have only one row in the "b" table that is of "Type A", you are going to get multiple rows returned from your query. It seems to me that you must refine your WHERE clause a bit.

Let us know your thoughts about these questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.