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)
-------------------------- ---"Progra mID=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'
-------------------------- ---"Progra mID=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'
-------------------------- ---"Progra mID=2" and "WorkFlowStage4" --> set VARS, update WorkFlowStage database column
update M
.........
-------------------------- ---"Progra mID=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
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 @ROLE varchar(100)
declare @mailSubject varchar(100)
declare @mailBody varchar(100)
declare @mailAddresses varchar(1000)
--------------------------
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'
--------------------------
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'
--------------------------
update M
.........
--------------------------
update M
.........
--------------------------
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 @mailBody = 'Please go to http://www.google.com/' + @ROLE + ' this is a test.'
--------------------------
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SABmail',
@recipients = @mailAddresses,
@body = @mailBody,
@subject = @mailSubject;
END
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.