Trigger to update linked tables.

Results 1 to 2 of 2

Thread: Trigger to update linked tables.

  1. #1
    Join Date
    Dec 1969

    Default Trigger to update linked tables.

    To keep things simple let&#039;s say I have 3 tables. MailList (list of email addresses), Campaign (list of email campaigns), Mail-Campaign (table where each email address is hooked to whatever campaigns it is subscribed to.<BR><BR>What I want to happen is that when a email address gets added to MailList a trigger fires and the Mail-Campaigns tables gets updated so the new email address get&#039;s all the campaigns.<BR><BR>Example<BR><BR>The tables has the following:<BR><BR>Campaign:<BR><BR>CampaignID Campaign name<BR>1 #1 Campaign<BR>2 #2 Campaign<BR>3 #3 Campaign<BR><BR>MailList:<BR><BR>MailID Email<BR>1<BR><BR>Mail-Campaign:<BR><BR>id MailID CampaignID<BR>1 1 1<BR>2 1 2<BR>3 1 3<BR><BR>After I add the new record to the MailList the MailList table and the Mail-Campaign tables should look like this:<BR><BR>MailList<BR><BR>MailID Email<BR>1<BR>2<BR><BR>Mail-Campaign:<BR><BR>id MailID CampaignID<BR>1 1 1<BR>2 1 2<BR>3 1 3<BR>4 2 1<BR>5 2 2<BR>6 2 3<BR><BR>How would I build this trigger?<BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default Why do you want to use a trigger???

    A trigger on insert seems to me to be a misuse of triggers.<BR><BR>After all, if you use a SP to do the insert, then it will be simpler *AND* faster to just have the SP go ahead and do the other inserts.<BR><BR>Actually, the other 3 inserts can be done by just one additional query.<BR><BR>INSERT INTO [Mail-Campaign] (MailID, CampaignID)<BR>SELECT @@IDENTITY, CampaignID FROM Campaigns<BR><BR>That picks up the identity of the just-added-to-MailList record along with each CampaignID from the Campaigns table and thus inserts one new record per CampaignID into the Mail-Campaign table. Tack that onto the end of the INSERT code in your SP and you are done.<BR><BR>

Posting Permissions

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