dcsimg
www.webdeveloper.com
Results 1 to 7 of 7

Thread: INSERT Trigger Help

  1. #1
    Join Date
    Feb 2006
    Posts
    4

    Unhappy INSERT Trigger Help

    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.

    Any help would be greatly appreciated!!

    Thanks,
    David

  2. #2
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    it really comes down to what you're going to be putting in there. what data's going in the table, what's going into the other tables, etc.

  3. #3
    Join Date
    Feb 2006
    Posts
    4
    Chazzy,
    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.

    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
    capID Customer dateOpened caseStatus
    21 Barney's 11/05/2005 Open

    tblBU
    buID busUnit
    4550 Marketing
    4551 Sales
    4552 Accounting
    4554 Human Resources

    tblCapBUMap
    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?

    Thanks again!
    David

  4. #4
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    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.

  5. #5
    Join Date
    Feb 2006
    Posts
    4
    Chazzy,

    I'm using MS SQL2000.

    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.

    Thanks!

  6. #6
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    unfortunately, I do not know ms sql server well enough to answer this. Here's a link @ this site's sister about creating ms sql server triggers:
    http://www.databasejournal.com/featu...le.php/1438641

    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.

  7. #7
    Join Date
    Feb 2006
    Posts
    4
    Chazzy,

    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.

    Thanks again

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles