#30 Auto-scubscribe users from custom fields

[Duane Collicott]

I have added two custom fields of type "user" ("Primary End User" and "Secondary End User") in which we can indicate the names of one or two end users who are the primary contacts for a bug. I wanted the users chosen in these fields to be automatically subscribed to the bug. to do this, I added a trigger on the users table.

Here's the text of the trigger:

CREATE TRIGGER trigAutosubscribeUsersToBug
ON bugs
AFTER INSERT, UPDATE
AS
BEGIN

set nocount on

declare @BugID int
declare @PrimaryEndUserID int
declare @SecondaryEndUserID int
declare @Exists int

-- Get the bug id and the id's of the Primary and Secondary End Users.
select @BugID=bg_id, @PrimaryEndUserID=[Primary End User], @SecondaryEndUserID=[Secondary End User]
from inserted

-- Check to see if a Primary End User was selected.
if (@PrimaryEndUserID <> 0)
begin
-- Make sure this user isn't already subscribed to this ticket.
select @Exists = count(*) from bug_subscriptions where bs_bug=@BugID and bs_user=@PrimaryEndUserID
if (@Exists = 0)
-- Do the subscribe.
insert into bug_subscriptions (bs_bug, bs_user) values (@BugID, @PrimaryEndUserID)
end

-- Check to see if a Secondary End User was selected.
if (@SecondaryEndUserID <> 0)
begin
-- Make sure this user isn't already subscribed to this ticket.
select @Exists = count(*) from bug_subscriptions where bs_bug=@BugID and bs_user=@SecondaryEndUserID
if (@Exists = 0)
-- Do the subscribe.
insert into bug_subscriptions (bs_bug, bs_user) values (@BugID, @SecondaryEndUserID)
end
end

[Duane Collicott]

Sorry... I meant to say I added it on the bugs table, not the users table.