Thanks for the reply.
The Cap table has information on the details of the escalation. The BU table is just a lookup table containing business Units (Marketing, Sales, etc.). So, when a new record is entered into tblCap, I would like the trigger to insert a record into tblCapBuMap that contains the new escaltions id (capID) and the key for the business units.
New record gets inserted into tblCap
INSERT INTO tblCap (customer, dateOpened, caseStatus)
VALUES (Barney's, 11/05/2005, Open)
Once inserted, initiate trigger/cursor to create a record in tblCapBuMap for the new escalation and each business unit:
SELECT * FROM tblBU
INSERT INTO tblCapBuMap (fk_capID, fk_buID, isPrimary)
VALUES (21, 4554, 0)
for each record in tblBU, so the tables would look like the following:
capID Customer dateOpened caseStatus
21 Barney's 11/05/2005 Open
4554 Human Resources
fk_capID fk_buID isPrimary
21 4550 0
21 4551 0
21 4552 0
21 4553 0
21 4554 0
Now that there would be corresponding records for every BU to each Cap, you can go in and set them as active by using the isPrimary field.
Does this make sense?