Link to home
Start Free TrialLog in
Avatar of finance_teacher
finance_teacher

asked on

Trigger -- MSSQL if/elsif ?

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
ASKER CERTIFIED SOLUTION
Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial