Hello. I'm new to Triggers, but based on my design, it seems like I need to get familiar.
Here's the scenario. Three related tables.
tblCap - capID, etc.
tblBU - buID, busUnit, etc.
tblCapBuMap - fk_capID, fk_buID, isPrimary
tblCap holds information about trouble ticket escalations, tblBU is a lookup table that contains business units that the trouble ticket can be assigned to (no limit). tblCapBuMap is a map table that will contain which business units are attached to a particular trouble ticket.
What I would like to accomplish is upon inserting a new record in tblCap, I would like to create new records in tblCapBuMap representing all business units (all records in tblBU).
Likewise, when inserting a new record in tblBU, I would need to create a new record in tblCapBuMap for every open record in tblCap.
I've looked through some trigger examples, but most examples don't seem to be anywhere near this complicated and thus I've got no idea where to turn.
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.
Example:
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: tblCap capIDCustomerdateOpenedcaseStatus
21 Barney's 11/05/2005 Open
what dbms are you using? trigger syntax differs from oracle, ms sql, etc.
the problem is that how do you know who's getting assigned what? if you only do an insert on one table, it can't see what you just inserted into another table. I don't see any relation between tblCap and tblBU aside from tblCapBUMap. It might be better to do this via a stored procedure.
There's no relation from tblCap to tblBU outside of the map table. At the point of entry of the new record in tblCap there may not be a business unit assigned, so there's no need to actually know at this point. The purpose is to have the records in the map table, so that when the user goes in to assign the business units, the relationship/records exist in the map table. The user simply "activates" the business unit in the map table via a form.
as for what you're doing, do you really need to have a separate table? it's a 1:1 correspondence (1 ticket goes to 1 BU) from what you've shown so you might not need it. all you're doing is inserting the new request id into the other table after insert.
Actually, there is no limit to the number of bu's assigned to a ticket. *It's terrible business rules*, but I don't make those rules, unfortunately. I just have to work around someone else's lazy mind.
So, one Cap can have eight BUs assigned to it, which is why I went with a map table so, I can create an unlimited amount of flexibility. The rules have changed over time. Originally, it started off as 1:1 then morphed to 1:4 and I kluged my way into a solution. Basically, they upped the ante again and I'm tired of programmatic changes because they are slackers, so I want to create a true 1:x relationship.
INSERT Trigger Help
Reply With Quote
Bookmarks