troubleshooting Question

Trigger -- MSSQL if/elsif ?

Avatar of finance_teacher
finance_teacher asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
1 Comment1 Solution425 ViewsLast Modified:
Below trigger works, but I want to merge the 20+ "UPDATE M" statements into less code.

How can I do something like the below ?
   if INSERTED I.ProgramID = '2' && I.WorkFlowStage = 1, then M.WorkFlowStage = 6
   elsif INSERTED I.ProgramID = '2' && I.WorkFlowStage = 2, then M.WorkFlowStage = 7
   elsif INSERTED I.ProgramID = '2' && I.WorkFlowStage = 4, then M.WorkFlowStage = 9
    etc
--------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER workflow
ON maint_work_req
      AFTER UPDATE, INSERT
AS
BEGIN
      -----------------------------declare vars
declare @ROLE varchar(100)
declare @mailSubject varchar(100)
declare @mailBody varchar(100)
declare @mailAddresses varchar(1000)

      -----------------------------"ProgramID=2" and "WorkFlowStage=1" --> set VARS, update WorkFlowStage database column
update M
      set WorkFlowStage = 6,
      @mailSubject = I.ID,
      @ROLE = '_EMAIL_2'
   from MAINT_WORK_REQ M
   join INSERTED I
      on I.ID = M.ID
   where M.WorkFlowStage = 1
      and I.ProgramID = '2' -- change to 'BUICK'
      -----------------------------"ProgramID=2" and "WorkFlowStage=2" --> set VARS, update WorkFlowStage database column
update M
      set WorkFlowStage = 7,
      @mailSubject = I.ID,
      @ROLE = '2_EMAIL_2'
   from MAINT_WORK_REQ M
   join INSERTED I
      on I.ID = M.ID
   where M.WorkFlowStage = 2
      and I.ProgramID  '2' -- change to 'BUICK'

      -----------------------------"ProgramID=2" and "WorkFlowStage4" --> set VARS, update WorkFlowStage database column
update M
   .........
      -----------------------------"ProgramID=2" and "WorkFlowStage<=7" --> set VARS, update WorkFlowStage database column
update M
   .........
      -----------------------------set var    
set @mailAddresses = (
              select up.email + ';'
              from userprofile up
              inner join webpages_Membership wpm
              on up.userID = wpm.userID
              inner join webpages_UsersInRoles wpur
              on wpm.userID = wpur.userID
              inner join webpages_Roles wpr
              on wpr.RoleID = wpur.RoleID
              where up.email is NOT NULL
              and RoleName = @ROLE -- change to I.ProgramID + '_EMAIL_2'
              FOR XML PATH('')
              )
      -----------------------------set var
set @mailBody = 'Please go to http://www.google.com/' + @ROLE + ' this is a test.'
      -----------------------------send email      

EXEC msdb.dbo.sp_send_dbmail
           @profile_name = 'SABmail',
           @recipients = @mailAddresses,
           @body = @mailBody,
           @subject = @mailSubject;
END
GO
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 1 Comment.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros